Database Design Process Requirements analysis IT420: Database - PDF document

Database Design Process � Requirements analysis IT420: Database Management and � Conceptual design: Entity-Relationship Organization Model � Logical design: transform ER model into relational schema The Relational Model � Schema refinement: Normalization (Chapter 3, pg 71-81) � Physical tuning 1 2 Goals Relational Database � Understand: � A relation is a two-dimensional table � The relational model � Relation schema describes the structure � Relational model terminology for the table � Relation name � Column names � Column types � A relational database is a set of relations 3 4 Relation Example Relation � All entries in a column are of the same kind EMPLOYEE(EmployeeNumber:integer, � Each column has a unique name FirstName:string, LastName:string, � Cells of the table hold a single value Department:string, � The order of the columns is not important Email:string, � The order of the rows is not important Phone:integer) � No two rows may be identical � Rows contain data about entity instances � Columns contain data about attributes of the entity 5 6 1

Alternative Terminology Tables That Are Not Relations � Although not all tables are relations, the terms table and relation are normally used interchangeably � The following sets of terms are equivalent: 8 9 Integrity Constraints (IC) Keys � IC: condition that must be true for any � A key is a combination of one or more columns that is used to identify rows in a relation instance of the database � A composite key is a key that consists of two or � ICs are specified when schema is defined more columns � ICs are checked when relations are � A set of columns is a key for a relation if : modified 1. No two distinct rows can have same values in � A legal instance of a relation is one that all key columns, and satisfies all specified ICs 2. This is not true for any subset of the key � DBMS should not allow illegal instances � Part 2 false? A superkey 10 11 Keys in EMPLOYEE Relation Candidate and Primary Keys � A candidate key is a key � A primary key is a candidate key selected as the primary means of identifying rows in a relation: � There is one and only one primary key per relation � The primary key may be a composite key � The ideal primary key is short, numeric and never changes 12 13 2

Surrogate Keys Surrogate Keys � A surrogate key as an artificial column NOTE: The primary key of the relation is underlined below: added to a relation to serve as a primary � RENTAL_PROPERTY without surrogate key: key: � DBMS supplied � Short, numeric and never changes – an ideal RENTAL_PROPERTY (Street, City, State/Province, Zip/PostalCode, Country, Rental_Rate) primary key! � Has artificial values that are meaningless to � RENTAL_PROPERTY with surrogate key: users RENTAL_PROPERTY (PropertyID, Street, City, � Remember Access State/Province, Zip/PostalCode, Country, Rental_Rate) 14 15 Foreign Keys and Referential Foreign Key with a Integrity Constraints Referential Integrity Constraint � A foreign key is the primary key of one NOTE: The primary key of the relation is underlined and any foreign keys are in italics in the relations below: relation that is placed in another relation to form a link between the relations DEPARTMENT (DepartmentName, BudgetCode, ManagerName) EMPLOYEE (EmployeeNumber, EmployeeName, � A referential integrity constraint: the DepartmentName ) values of the foreign key must exist as Where EMPLOYEE.DepartmentName must exist in primary key values in the corresponding DEPARTMENT.DepartmentName relation � No ‘dangling references’ � Can you name a data model w/o referential integrity? 16 17 ICE: Is This a Relation? Why? ICE:Find PK, AK X Y Z W A X C A John Ryan MD jr@gmail.com John Ryan MD jr@gmail.com Bob Smith MD bsm@gmail.com Bob Smith MD, bsm@gmail.com VA, Alice Brown CA NY John Doe WA jd@yahoo.com Alice Brown CA Jane Doe WA jd@yahoo.com John Ryan MD jr@gmail.com 5 4 5 4 18 19 3

Summary – Relational Model � 2-D tables � Relational schema: structure of table � Constraints � Domain � Key � Candidate, Primary, Alternate, Surrogate � Foreign key – Referential integrity constraint 20 4