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 Entity-Relationship Model to � Schema refinement: Normalization Relational Model � Physical tuning (Chapter 6) 1 2 Goals ER Model � Entities, attributes, identifiers � Transform ER model to relational model � Weak entities � Write SQL statements to create tables � ID-dependent entities - identifying relationships � HAS-A Relationships � Degree: binary, ternary � Maximum cardinality: 1:1, 1:N, N:M � Minimum cardinality: O-O, O-M, M-O, M-M � IS-A Relationships (Super-type / sub-type) � Inclusive, Exclusive VERY IMPORTANT to design a good model! 3 4 Relational Model SQL - The Language of Databases � Tables � Developed by IBM in the 1970s � Integrity constraints � Create and process database data � Primary key � SQL programming is a critical skill !!! � Candidate key � Foreign key 5 6 1

ER to Relational Create a Table for Each Entity � Transform entities in tables � CREATE TABLE statement is used for creating � Transform relationships using foreign keys relations/tables � Specify logic for enforcing minimum � Each column is described cardinalities with three parts: � column name � data type � optional constraints 7 9 Specify Data Types Specify Null Status CREATE TABLE EMPLOYEE ( � Null status: � Choose the most specific data type EmployeeNumber integer NOT whether or not possible!!! NULL, the value of the EmployeeName char (50) NOT � Generic Data Types: column can be � CHAR(n) NULL, CREATE TABLE EMPLOYEE ( NULL � VARCHAR(n) EmployeeNumber integer, Phone char (15) NULL, � DATE EmployeeName char(50), Email char(50) NULL, � TIME Phone char(15), HireDate date NOT NULL, � MONEY Email char(50), � INTEGER ReviewDate date NULL HireDate date, � DECIMAL ) ReviewDate date ) 10 11 Specify Default Values Specify Other Data Constraints � Data constraints are limitations on data values � Default value - value supplied by the DBMS, if no value is specified when a row is inserted CREATE TABLE EMPLOYEE ( Name for EmployeeNumber integer NOT NULL, constraint Syntax/support CREATE TABLE EMPLOYEE ( EmployeeName char (50) NOT NULL, depends on DBMS EmployeeNumber integer NOT NULL, Phone char (15) NULL, EmployeeName char (50) NOT NULL, Email char(50) NULL, Phone char (15) NULL, HireDate date NOT NULL DEFAULT (getdate()), Email char(50) NULL, ReviewDate date NULL, HireDate date NOT NULL DEFAULT (getdate()), CONSTRAINT Check_Email CHECK (Email LIKE ‘%@gmail.com’) ReviewDate date NULL ) ) 12 14 2

Specify Primary Key Specify Alternate Keys � � Entity identifier � primary key (usually) Alternate keys: alternate identifiers of unique rows in a table CREATE TABLE EMPLOYEE ( CREATE TABLE EMPLOYEE ( EmployeeNumber integer NOT NULL, EmployeeNumber integer NOT NULL, EmployeeName char (50) NOT NULL, EmployeeName char (50) NOT NULL, Phone char (15) NULL, Phone char (15) NULL, Email char(50) NULL, Email char(50) NULL, HireDate date NOT NULL DEFAULT (getdate()), HireDate date NOT NULL DEFAULT (getdate()), ReviewDate date NULL, ReviewDate date NULL, CONSTRAINT Check_Email CHECK (Email LIKE ‘%@gmail.com’), CONSTRAINT Check_Email CHECK (Email LIKE ‘%@gmail.com’), CONSTRAINT PK_Employee PRIMARY KEY (EmployeeNumber), CONSTRAINT PK_Employee PRIMARY KEY (EmployeeNumber) CONSTRAINT AK_Email UNIQUE (Email), CONSTRAINT AK_ENamePhone UNIQUE (EmployeeName, ) Phone) ) 16 17 Create Relationships: ER to Relational 1:1 Strong Entity Relationships � Transform entities in tables � Place the key of one entity in the other entity as a � Transform relationships using foreign keys foreign key: � Specify logic for enforcing minimum � Either design will work – no cardinalities parent, no child � Minimum cardinality considerations may be important: � O-M will require a different design that M-O 18 19 Create Relationships: Create Relationships: 1:1 Strong Entity Relationships 1:1 Strong Entity Relationships CREATE TABLE CLUB_MEMBER( CREATE TABLE CLUB_MEMBER( MemberNumber integer PRIMARY KEY, MemberNumber integer PRIMARY KEY MemberName char(50), MemberName char(50), Phone char(15), Phone char(15), Email char(50)) Email char(50), LockerNumber integer NULL, CREATE TABLE LOCKER( CONSTRAINT FK_Locker FOREIGN KEY (LockerNumber) REFERENCES LOCKER(LockerNumber), LockerNumber integer PRIMARY KEY, CONSTRAINT Unique_Locker UNIQUE(LockerNumber)) LockerRoom integer, LockerSize integer, MemberNumber integer NULL , CREATE TABLE LOCKER( CONSTRAINT FK_Member FOREIGN KEY (MemberNumber) REFERENCES CLUB_MEMBER(MemberNumber), LockerNumber integer PRIMARY KEY, CONSTRAINT Unique_Member UNIQUE(MemberNumber)) LockerRoom integer, LockerSize integer) 21 22 3

Create Relationships: Create Relationships: 1:N Relationships 1:N Strong Entity Relationships � “Place the key of the parent in the child” CREATE TABLE COMPANY( CREATE TABLE DEPARTMENT( CompanyName char(50) DepartmentName char(50) PRIMARY KEY, PRIMARY KEY, BudgetCode char(5), City char(50), MailStop integer, Country char(50), CompanyName char(50) NOT NULL, Volume decimal) CONSTRAINT FK_Company FOREIGN KEY (CompanyName) REFERENCES COMPANY (CompanyName)) 23 24 Create Relationships: Create Relationships: 1:N Identifying Relationship N:M Strong Entity Relationships CREATE TABLE BUILDING( � In an N:M relationship there is no place for the BuildingName char(50) PRIMARY KEY, Street varchar(50), foreign key in either table: City char(50), � A COMPANY may supply many PARTs State char(30), � A PART may be supplied by many COMPANYs Zip integer) CREATE TABLE APARTMENT( ApartmentNumber integer NOT NULL, BuildingName char(50) NOT NULL, NumberBedrooms integer, NumberBaths integer, MonthlyRent decimal, CONSTRAINT PK_Apartment PRIMARY KEY (BuildingName, ApartmentNumber), CONSTRAINT FK_Building FOREIGN KEY (BuildingName) REFERENCES BUILDING (BuildingName)) 25 26 Create Relationships: Create Relationships: N:M Strong Entity Relationships N:M Strong Entity Relationships � Create an intersection table: CREATE TABLE COMPANY( CompanyName char(50) PRIMARY KEY, City char(50), � The primary keys of each table � composite Country char(50), Volume decimal) primary key for intersection table PART( PartNumber integer PRIMARY KEY, � Each table’s primary key becomes a PartName char(50), SalesPrice decimal, foreign key linking back to that table ReOrderQuantity integer, QuantityOnHand integer) COMPANY_PART( CompanyName char(50) NOT NULL, PartNumber integer NOT NULL, CONSTRAINT PK_CompPart PRIMARY KEY (CompanyName, PartNumber), CONSTRAINT FK_Company FOREIGN KEY (CompanyName) REFERENCES COMPANY (CompanyName), CONSTRAINT FK_Part FOREIGN KEY (PartNumber) REFERENCES PART (PartNumber)) 27 28 4

Subtype Relationships ER to Relational CREATE TABLE EMPLOYEE( � Transform entities in tables EmployeeNumber integer PRIMARY KEY, …) � Transform relationships using foreign keys CREATE TABLE MANAGER( EmployeeNumber integer PRIMARY KEY, � Specify logic for enforcing minimum MgrTrainingDate date, ManagerLevel integer, cardinalities CONSTRAINT FK_Emp FOREIGN KEY (EmployeeNumber) REFERENCES EMPLOYEE (EmployeeNumber) ) CREATE TABLE DB_ADMIN( EmployeeNumber integer PRIMARY KEY, DB_Name char(50), DBMS char(50), CONSTRAINT FK_Emp FOREIGN KEY (EmployeeNumber) REFERENCES EMPLOYEE (EmployeeNumber) 29 30 ) FOREIGN KEY Constraints FOREIGN KEY Constraints CREATE TABLE Students � 4 options on DepartmentName Phone Building Room DEPARTMENTS (StudentNumber integer, DepartmentName: char(18) Mathematics 410-293-4573 Michelson Hall 308 deletes and StudentLastName char(18) NOT NULL, History 410-293-2255 Sampson Hall 120 Phone: char(18) Building: char(18) StudentFirstName char(18) NOT NULL, Computer Science 410-293-6800 Michelson Hall 340 Room: integer updates Email char(50), D:SN PhoneNumber char(18), Student Student Student Email PhoneNumber MajorDepartmentName U:C � NO ACTION MajorDepartmentName char(18), Number LastName FirstName 190 Smith John jsmith@usna.edu 410-431-3456 PRIMARY KEY (StudentNumber), Majors (default option) 673 Doe Jane jdoe@usna.edu Computer Science UNIQUE(Email), 312 Doe Bob bred@usna.edu 443-451-7865 Mathematics CONSTRAINT FK_Dept FOREIGN KEY I:SN � CASCADE U:SN (MajorDepartmentName) CREATE TABLE Departments STUDENTS � SET NULL REFERENCES Departments (DepartmentName) StudentNumber: integer (DepartmentName char(18), ON DELETE SET NULL StudentLastName: char(18) Phone char(18) NOT NULL, � SET DEFAULT ON UPDATE CASCADE StudentFirstName: char(18) Building char(18), Email: varchar(50) ) PhoneNumber: char(18) Room integer, DepartmentName: char(18) (FK) PRIMARY KEY (DepartmentName) ) 31 32 Enforcing Mandatory Parent Enforcing Mandatory Child � More difficult to enforce (write code – DEPARTMENT (DepartmentName, BudgetCode, ManagerName) “triggers”) CREATE TABLE EMPLOYEE ( EmployeeNumber integer PRIMARY KEY, DEPARTMENT (DepartmentName, BudgetCode, ManagerName) EmployeeName char(50), EMPLOYEE (EmployeeNumber, EmployeeName, DepartmentName char(50) NOT NULL, DepartmentName ) CONSTRAINT FK_Dept FOREIGN KEY(DepartmentName) � Tricky: REFERENCES DEPARTMENT(DepartmentName) ON DELETE NO ACTION � A department must have some employee ON UPDATE CASCADE ) � EMPLOYEE has DepartmentName as FK, NOT NULL 33 34 5