CS206 --- Electronic Commerce

CS206 --- Electronic Commerce

Instructor: Jinze Liu Fall 2008 Phases of Database Design Data Requirements Specification of requirements and results Conceptual Design Conceptual Schema Logical Design

Logical Schema Physical Design Physical Schema Conceptual design begins with the collection of requirements and results needed from the database (ER Diag.) Logical schema is a description of the structure of the database (Relational, Network, etc.) Physical schema is a description of the implementation (programs, tables, dictionaries, catalogs 2 A data model is a collection of objects that

can be used to represent a set of data and operations to manipulate the data Conceptual models are tools for representing reality at a very high-level of abstraction Logical models are data descriptions that can be processed by computers 3 Entities represent classes of real-world objects. Person, Students, Projects, Courses are entities of a University database Relationships represent interactions between two or more entities 1 N

Lives_In Person City Is_Born_In N 1 4 Every employee works in at least one project Every project has employees working on it. EMPLOYEE SSN Name Salary

N WORKS_ON N PROJECT Name Code 5 A relationship may involve more than two entities Course Meets

Classroom Day 6 Relationships could be mapped from one entity to itself Manager_Of 1 Employee Manages N Subordinate_To 7 Attributes represent elementary properties of the entities or relationships. The stored data

will be kept as values of the attributes Lives_In N PERSON Ssn Name Profession 1 CITY Name Country Elevation Population Moving_Date Birth_Date

Is_Born_In N 1 8 name population Cities In States name capital

county_name county_area County area information is repeated for every city in the county Redundancy is bad. What else? State capital should really be a city Should reference entities through explicit relationships 02/11/20 11 name population

Cities In name area Counties IsCapitalOf In States name Technically, nothing in this design could prevent a city in state X from being the

capital of another state Y, but oh well 02/11/20 12 Relation schema = relation name + attributes, in order (+ types of attributes). Example: Beers(name, manf) or Beers(name: string, manf: string) Database = collection of relations. Database schema = set of all relation schemas in the database. 14

Very simple model. Often matches how we think about data. Abstract model that underlies SQL, the most important database language today. But SQL uses bags, while the relational model is a set-based model. 15 Entity sets become relations with the same set of attributes. Relationships become relations whose attributes are only: The keys of the connected entity sets. Attributes of the relationship itself.

16 name manf Beers Relation: Beers(name, manf) 17 name husband addr

Drinkers 1 name Likes manf Beers 2 Buddies Favorite wife Married

Likes(drinker, beer) Favorite(drinker, beer) Buddies(name1, name2) Married(husband, wife) 18 It is OK to combine the relation for an entity- set E with the relation R for a many-one relationship from E to another entity set. Example: Drinkers(name, addr) and Favorite(drinker, beer) combine to make Drinker1(name, addr, favBeer). 19 Combining Drinkers with Likes would be a mistake. It leads to redundancy, as:

name Sally Sally addr beer 123 Maple 123 Maple Bud Miller Redundancy 20 Relation for a weak entity set must include

attributes for its complete key (including those belonging to other entity sets), as well as its own, nonkey attributes. A supporting (double-diamond) relationship is redundant and yields no relation. 21 name time Logins name At Hosts Hosts(hostName)

Logins(loginName, hostName, time) At(loginName, hostName, hostName2) At becomes part of Logins Must be the same 22 A database is a collection of relations (or tables) Each relation is identified by a name and a list of attributes (or columns) Each attribute has a name and a domain (or type) Set-valued attributes not allowed

02/11/20 23 Schema (metadata) Specification of how data is to be structured logically Defined at set-up Rarely changes Instance Content Changes rapidly, but always conforms to the schema Compare to type and objects of type in a programming language 02/11/20

24 Schema Student (SID integer, name string, age integer, GPA float) Course (CID string, title string) Enroll (SID integer, CID integer) Instance { h142, Bart, 10, 2.3i, h123, Milhouse, 10, 3.1i, ...} { hCPS116, Intro. to Database Systemsi, ...} { h142, CPS116i, h142, CPS114i, ...} 02/11/20 25

Constraints are conditions that must hold on all valid relation instances. There are four main types of constraints: 1. Domain constraints 1. The value of a attribute must come from its domain Key constraints 3. Entity integrity constraints 4. Referential integrity constraints 2.

02/11/20 26 A set of fields is a candidate key for a relation if : 1. No two distinct tuples can have same values in all key fields, and 2. This is not true for any subset of the key. Part 2 false? A superkey. If theres >1 key for a relation, one of the keys is chosen (by DBA) to be the primary key. E.g., given a schema Student(sid: string, name: string, gpa: float) we have: sid is a key for Students. (What about name?) The set {sid, gpa} is a superkey.

02/11/20 Jinze Liu @ University of Kentucky 27 CAR (licence_num: string, Engine_serial_num: string, make: string, model: string, year: integer) 02/11/20 What is the candidate key(s) Which one you may use as a primary key

What are the super keys 28 Entity Integrity: The primary key attributes PK of each relation schema R in S cannot have null values in any tuple of r(R). Other attributes of R may be similarly constrained to disallow null values, even though they are not members of the primary key. 02/11/20 29 : Set of fields in one relation that is

used to `refer to a tuple in another relation. (Must correspond to primary key of the second relation.) Like a `logical pointer. E.g. sid is a foreign key referring to Students: Foreign key Student(sid: string, name: string, gpa: float) Enrolled(sid: string, cid: string, grade: string) If all foreign key constraints are enforced, referential integrity is achieved, i.e., no dangling references. Can you name a data model w/o referential integrity?

Links in HTML! 02/11/20 Jinze Liu @ University of Kentucky 30 Only students listed in the Students relation should be allowed to enroll for courses. Enrolled sid 53666 53666 53650 53666

cid Carnatic101 Reggae203 Topology112 History105 grade C B A B Students sid 53666 53688 53650 name

login Jones [email protected] Smith [email protected] Smith [email protected] age gpa 18 3.4 18 3.2 19 3.8 Or, use NULL as the value for the foreign key in the referencing tuple when the referenced tuple does not exist 02/11/20 Jinze Liu @ University of Kentucky 31

Semantic Integrity Constraints: based on application semantics and cannot be expressed by the model per se e.g., the max. no. of hours per employee for all projects he or she works on is 56 hrs per week A constraint specification language may have to be used to express these SQL-99 allows triggers and ASSERTIONS to allow for some of these 02/11/20 32

Recently Viewed Presentations

  • General Birch Certificate of Freedom - Welcome to Mrs ...

    General Birch Certificate of Freedom - Welcome to Mrs ...

    General Birch Certificate of Freedom Write a paragraph describing the General Birch Certificate of Freedom. Keep the OPVL in mind, along with these key questions. What does this document promise/offer? How will people like Cato decide on a place?
  • Economic Growth

    Economic Growth

    Arial Calibri 宋体 Default Design Economic Reforms (1978 - ) People's Republic of China Mao's Legacies Industrialization in 1950s Mao Zedong Died in 1976 Incentives for High Growth Maoist Growth Unsustainable Strategic Vision Structural Imperative PowerPoint Presentation Transition Strategy Economic...
  • Exchange 2010 Webcasts - download.microsoft.com

    Exchange 2010 Webcasts - download.microsoft.com

    Exchange 2010 Archiving & Retention. obiettivi e design goal. Exchange Personal Archive. ... OLK/OWA Integration. No user search for data . Issues with stubs. NA. Cost and Compliance Toolset for IT Pro/Records Mgr. SANs cost limits size. Backup costs limit...
  • Causes of The Great Depression The Great Depression

    Causes of The Great Depression The Great Depression

    There had been credit before for businesses, but this was the first time personal consumer credit was available. By the end of the 1920s, 60% of the cars and 80% of the radios were bought on installment credit. ... dual...


    E-electioneering and E-democracy (Government 2.0) in Australia Studies of online citizen consultation and social media in the 2010 Australian federal election


    Go to uw.edu/brand/fonts. Download three fonts: Encode Sans, Uni Sans, Open Sans. You may need to close PowerPoint and re-open in order to access the fonts. All templates themes include these brand fonts. YOU WILL NEED TO INSTALL FONTS TO...
  • Field Trip Prep. - CLAS Users

    Field Trip Prep. - CLAS Users

    Basically refraction is brought about by waves moving faster in deeper water than in shallow water. * Wave Shoaling - L & C Deep water - L, C depend only on period Shallow water - L, C depend only on...
  • SS8H6a Events Leading to the Civil War  2014

    SS8H6a Events Leading to the Civil War 2014

    Events Leading to the. Civil War - CLOZE Notes 3. KEY. Dred Scott Case. Another event that sparked anger was the Dred Scott case in 1857. Dred Scott, a Missouri slave, sued for his freedom because he had lived for...