IST 210 DATABASE DESIGN I IST 210 Organization

IST 210 DATABASE DESIGN I IST 210: Organization of Data 1

IST 210 I want a database to maintain departments in my company. Store information about my employees, their projects and assignments. I want …. User requirement Data Modeling (Ch. 4) $$$ Database Design (Ch. 5) A database 2 YOUR JOB!

IST 210 3 Two-Step Approach: Step 1 Data Modeling (Ch. 4) Database Design (Ch. 5) • Input: User requirement; Output: E-R Diagram • Use Entity-Relationship Diagram (E-R Diagram) to capture all user requirements

IST 210 Two-Step Approach: Step 2 Data Modeling (Ch. 4) Database Design (Ch. 5) • Input: E-R diagram; Output: A database • Transferring a data model to a relational database • Entities Relations • Relationships Foreign keys and extra 4

IST 210 5 Chapter Key Objective Data Model (E-R Diagram) Relational Model (Tables) 1. Entity Table • Entity name Table name • Identifier Primary key • Attributes 2. Relationship Foreign key or a new table • Determined by the maximal cardinality 3. Specify the attribute properties • Data type • Key • Required • Determined by the minimal cardinality • Remarks

IST 210 E-R Diagram Tables 1. Entity Table • Entity name Table name • Identifier Primary key • Attributes 2. Relationship Foreign key or a new table 3. Specify the attribute properties 6

7 IST 210 Example 1) Entity name Table name 2) Identifier Primary key 3) Attributes EMPLOYEE LOCKER Employee. ID Locker. ID Name Office. Number Office. Phone Locker. Room Locker. Size EMPLOYEE(Employee. ID, Name, Office. Number, Office. Phone) LOCKER(Locker. ID, Locker. Room, Locker. Size) *primary key: use underline

IST 210 E-R Diagram Tables 1. Entity Table • Entity name Table name • Identifier Primary key • Attributes 2. Relationship Foreign key or a new table • Determined by the maximal cardinality 3. Specify the attribute properties 8

9 IST 210 Maximal Cardinality • 1: 1 (one-to-one) Relationship Entity A Entity B • 1: N (one-to-many) Relationship Entity A Entity B • N: M (many-to-many) Relationship Entity A Entity B

10 IST 210 1: 1 Relationship EMPLOYEE(Employee. ID, Name, Office. Number, Office. Phone) LOCKER(Locker. ID, Locker. Room, Locker. Size) LOCKER EMPLOYEE Employee ID Name Office Number Office Phone Locker ID Locker Room Locker Size 4 Tom 281 9182 1 #2 10 5 #2 5 Relationship is not captured yet! Add foreign key ?

11 IST 210 Employee 4 owns locker 1; locker 1 belongs to employee 4. Option A EMPLOYEE LOCKER Employe e ID Name Office Number Office Phone Locker ID Locker Room Locker Size 4 Tom 281 9182 1 1 #2 10 5 #2 5 Option B EMPLOYEE LOCKER Employe e ID Name Office Number Office Phone Locker ID Locker Room Locker Size Employe e. ID 4 Tom 281 9182 1 #2 10 4 5 #2 5 Option C EMPLOYEE LOCKER Employe e ID Name Office Number Office Phone Locker ID 4 Tom 281 9182 1 Locker ID Locker Room Locker Size Employe e. ID 1 #2 10 4 5 #2 5

IST 210 12 1: 1 Relationship *foreign key: italic Option A EMPLOYEE(Employee. ID, Name, Office. Number, Office. Phone, Locker. ID) LOCKER(Locker. ID, Locker. Room, Locker. Size) Option B EMPLOYEE(Employee. ID, Name, Office. Number, Office. Phone) LOCKER(Locker. ID, Locker. Room, Locker. Size, Employee. ID) Both options are correct. Option A is preferred. • If choosing Option A, since every employee has one locker, Locker. ID must have a value in EMPLOYEE table. • If choosing Option B, since some lockers does not belong to anyone, Employee. ID will be NULL value for some lockers and it takes extra space. (See the previous slide, Option B needs one more cell. )

IST 210 13 1: 1 Relationship • The maximum cardinality determines how a relationship is represented • 1: 1 relationship • The key from one relation is placed in the other as a foreign key • It does not matter which table receives the foreign key • We prefer the option taking less space

14 IST 210 1: N Relationship EMPLOYEE LOCKER Employee. ID Locker. ID Name Office. Number Office. Phone Locker. Room Locker. Size Assume one-to-many relationship LOCKER EMPLOYEE Employee ID Last Name First Name Office Number Office Phone Locker ID Locker Room Locker Size 4 Green Tom 281 9182 1 #2 10 5 #2 5 Relationship is not captured yet! Add foreign key ?

15 IST 210 Employee 4 owns locker 1 and 5. Option A EMPLOYEE LOCKER Employe e ID Name Office Number Office Phone Locker ID Locker Room Locker Size 4 Tom 281 9182 1, 5 1 #2 10 5 #2 5 Option B EMPLOYEE LOCKER Employe e ID Name Office Number Office Phone Locker ID Locker Room Locker Size Employee ID 4 Tom 281 9182 1 #2 10 4 5 #2 5 4 Option C EMPLOYEE LOCKER Employe e. ID Name Office Number Office Phone Locker ID Locker Room Locker Size Employee ID 4 Tom 281 9182 1, 5 1 #2 10 4 5 #2 5 4

IST 210 16 1: N Relationship EMPLOYEE(Employee. ID, Name, Office. Number, Office. Phone) LOCKER(Locker. ID, Locker. Room, Locker. Size, Employee. ID)

IST 210 17 1: N Relationship • Like a 1: 1 relationship, a 1: N relationship is saved by placing the key from one table into another as a foreign key • However, in a 1: N the foreign key always goes into the many-side of the relationship • The 1 side is called the parent • The N side is called the child

18 IST 210 N: M Relationship EMPLOYEE LOCKER Employee. ID Locker. ID Name Office. Number Office. Phone Locker. Room Locker. Size Assume many-to-many relationship LOCKER EMPLOYEE Employee ID Name Office Number Office Phone Locker ID Locker Room Locker Size 4 Tom 281 9182 1 #2 10 10 John 777 1829 5 #2 5 Relationship is not captured yet! Add foreign key ?

19 IST 210 Employee 4 owns locker 1 and 5. Employee 10 owns locker 1. Option A EMPLOYEE LOCKER Employe e ID Name Office Number Office Phone Locker ID Locker Number Locker Room Locker Size 4 Tom 281 9182 1, 5 1 #2 10 10 John 777 1829 1 5 #2 5 Option B EMPLOYEE LOCKER Employe e ID Name Office Number Office Phone Locker ID Locker Room Locker Size Employe e. ID 4 Tom 281 9182 1 #2 10 4, 10 10 John 777 1829 5 #2 5 4

20 IST 210 Employee 4 owns locker 1 and 5. Employee 10 owns locker 1. EMPLOYEE LOCKER Employe e ID Name Office Number Office Phone Locker ID Locker Room Locker Size 4 Tom 281 9182 1 #2 10 10 John 777 1829 5 #2 5 ASSIGNMENT Employee ID Locker ID 4 1 4 5 10 1 NOT SIMPLY ADDING FOREIGN KEYS! CREATE ANOTHER TABLE!

IST 210 21 N: M Relationship EMPLOYEE(Employee. ID, Last. Name, First. Name, Office. Number, Office. Phone) LOCKER(Locker. ID, Locker. Room, Locker. Size) ASSIGNMENT(Employee. ID, Locker. ID) Note: Employee. ID and Locker. ID are both primary keys and foreign keys in ASSIGNMENT table

IST 210 22 N: M Relationship • To represent a N: M relationship in relational design, a new table must be created. • This table is called an intersection table • An intersection table has a composite key consisting of the keys from each of the tables that it connects

23 IST 210 Relationship Summary 1: 1 add a foreign key to either table (preferred) EMPLOYEE(Employee. ID, Name, Office. Number, Office. Phone, Locker. ID) LOCKER(Locker. ID, Locker. Room, Locker. Size) or EMPLOYEE(Employee. ID, Name, Office. Number, Office. Phone) LOCKER(Locker. ID, Locker. Room, Locker. Size, Employee. ID) 1: N add a foreign key to the many-side table EMPLOYEE(Employee. ID, Name, Office. Number, Office. Phone) LOCKER(Locker. ID, Locker. Room, Locker. Size, Employee. ID) N: M add an intersection table EMPLOYEE(Employee. ID, Name, Office. Number, Office. Phone) LOCKER(Locker. ID, Locker. Room, Locker. Size) ASSIGNMENT(Employee. ID, Locker. ID)

IST 210 In-class Exercise 1 Transform this diagram into tables PARKING USER Spot. ID User. ID Location Monthly. Cost Name Email 24

IST 210 In-class Exercise 1 Transform this diagram into tables PARKING USER Spot. ID User. ID Location Monthly. Cost Name Email PARKING(Spot. ID, Location, Monthly. Cost) USER(User. ID, Name, Email, Spot. ID) 25

IST 210 26 In-class Exercise 1: Notes PARKING(Spot. ID, Location, Monthly. Cost) USER(User. ID, Name, Email, Spot. ID) PARKING(Spot. ID, Location, Monthly. Cost, User. ID) USER(User. ID, Name, Email) Note: both options are correct, but the first one is preferred because of the minimal cardinality of Spot. ID is mandatory.

IST 210 In-class Exercise 2 Transform this diagram into tables BUILDING APARTMENT Building. Name Apartment. ID Address Number. Of. Bed Number. Of. Bath Rent 27

IST 210 28 In-class Exercise 2 Transform this diagram into tables BUILDING(Building. Name, Address) APARTMENT(Building. Name, Apartment. ID, Number. Of. Bedrooms, Numberof. Baths, Rent)

IST 210 29 In-class Exercise 2: Notes BUILDING(Building. Name, Address) APARTMENT(Building. Name, Apartment. ID, Number. Of. Bedrooms, Numberof. Baths, Rent) Notes: * A common mistake is making Apartment. ID as the only primary key. (Building. Name, Apartment. ID) should a composite key for APARTMENT * Another common mistake is to create a duplicate attribute Building. Name in APARTMENT. Or forget to make Building. Name as the foreign key

30 IST 210 In-class Exercise 3 Transform this diagram into tables STUDENT COURSE Student. ID Course. ID Student. Name Email Course. Name Instructor

31 IST 210 In-class Exercise 3 Transform this diagram into tables STUDENT COURSE Student. ID Course. ID Student. Name Email Course. Name Instructor STUDENT(Student. ID, Student. Name, Email) COURSE(Course. ID, Course. Name, Instructor) REGISTRATION(Student. ID, Course. ID)

IST 210 32 In-class Exercise 3: Notes STUDENT(Student. ID, Student. Name, Email) COURSE(Course. ID, Course. Name, Instructor) REGISTRATION(Student. ID, Course. ID) Notes: * In REGISTRATION table, Studet. ID and Course. ID are both primary key and foreign key. So you should give both underline and italic (wave underline in handwritten format) to them.

IST 210 E-R Diagram Tables 1. Entity Table • Entity name Table name • Identifier Primary key • Attributes 2. Relationship Foreign key or a new table • Determined by the maximal cardinality • 1: 1 add a foreign key to either table • 1: N add a foreign key to the many-side table • N: M add an intersection table 3. Specify the attribute properties 33

IST 210 E-R Diagram Tables 1. Entity Table • Entity name Table name • Identifier Primary key • Attributes 2. Relationship Foreign key or a new table • Determined by the maximal cardinality 3. Specify the attribute properties • Data type • Key • Required • Determined by the minimal cardinality • Remarks 34

IST 210 35 EMPLOYEE(Employee. ID, Name, Office. Number, Office. Phone, Locker. ID) LOCKER(Locker. ID, Locker. Room, Locker. Size) EMPLOYEE table Column Name Data Type Key Required Remarks Employee. ID Name Office. Number Office. Phone Locker. ID LOCKER table Column Name Locker. ID Locker. Room Locker. Size

IST 210 36 EMPLOYEE(Employee. ID, Name, Office. Number, Office. Phone, Locker. ID) LOCKER(Locker. ID, Locker. Room, Locker. Size) EMPLOYEE table Column Name Data Type Employee. ID int Name char(50) Office. Number char(20) Office. Phone char(12) Locker. ID int LOCKER table Column Name Data Type Locker. ID int Locker. Room char(10) Locker. Size float Data Type • Determine the data types based on the type of these attributes • Data types must be the types defined in SQL • http: //technet. microsoft. com/enus/library/ms 187752. aspx • Data type of a foreign key must be the same type as its referred primary key • Locker. ID in EMPLOYEE table must have the same data type as Locker. ID in LOCKER because Locker. ID is a foreign key in EMPLOYEE

IST 210 37 EMPLOYEE(Employee. ID, Name, Office. Number, Office. Phone, Locker. ID) LOCKER(Locker. ID, Locker. Room, Locker. Size) EMPLOYEE table Column Name Key Employee. ID Primary key Name Office. Number Office. Phone Locker. ID Foreign key LOCKER table Column Name Data Type Locker. ID Primary key Locker. Room Locker. Size Key • Specify primary key and foreign key(s) in the table

IST 210 38 EMPLOYEE(Employee. ID, Name, Office. Number, Office. Phone, Locker. ID) LOCKER(Locker. ID, Locker. Room, Locker. Size) EMPLOYEE table Column Name Required Employee. ID Yes Name Yes Office. Number No Office. Phone No Locker. ID Yes LOCKER table Column Name Required Locker. ID Yes Locker. Room Yes Locker. Size Yes Required • Whether an attribute is required or not determines whether we allow NULL value for this attribute • Primary key must be required • Normal attributes are determined based on requirements or common sense • “not required” is preferred • Foreign key is determined by minimum cardinality • An employee must have at least one locker. So Locker. ID is required in EMPLOYEE

IST 210 39 EMPLOYEE(Employee. ID, Name, Office. Number, Office. Phone, Locker. ID) LOCKER(Locker. ID, Locker. Room, Locker. Size) EMPLOYEE table Column Name Remarks Employee. ID Surrogate key: initial value = 1 Increment = 1 Name Office. Number Office. Phone Format: ###-#### Locker. ID Reference: LOCKER table Column Name Remarks Locker. ID Surrogate key: initial value = 1 Increment = 1 Locker. Room Locker. Size Default value: 10 Remarks • If a primary key is a surrogate key, specify the initial value and increment • If an attribute is a foreign key, specify which table it refers to • If an attribute has a required format, specify the format • If an attribute has a default value, specify the value

40 IST 210 EMPLOYEE(Employee. ID, Name, Office. Number, Office. Phone, Locker. ID) LOCKER(Locker. ID, Locker. Room, Locker. Size) LOCKER table EMPLOYEE table Column Name Data Type Key Required Remarks Employee. ID int Primary key Yes Surrogate key: initial value = 1 Increment = 1 Locker. ID int Primary key Yes Name char(50) Yes Surrogate key: initial value = 1 Increment = 1 Office. Number char(20) No Locker. Room char(10) Yes Office. Phone char(12) No Format: ###-#### Locker. Size float Yes Locker. ID int Yes Reference: LOCKER Foreign key Now we complete converting an E-R diagram to tables Next, we can use SQL to create a real database Default value: 10

IST 210 In-class Exercise 1 PARKING(Spot. ID, Location, Monthly. Cost) USER(User. ID, Name, Email, Spot. ID) 41

IST 210 In-class Exercise 1 PARKING(Spot. ID, Location, Monthly. Cost) USER(User. ID, Name, Email, Spot. ID) PARKING table Column Name Data Type Key Required Remarks Spot. ID Location Monthly. Cost USER table Column Name User. ID Name Email Spot. ID 42

IST 210 PARKING(Spot. ID, Location, Monthly. Cost) USER(User. ID, Name, Email, Spot. ID) PARKING table Column Name Data Type Key Required Remarks Spot. ID int Primary key Yes Location char(50) Yes Monthly. Cost int Yes Surrogate key: initial value = 1 Increment = 1 Default: 50 USER table Column Name Data Type Key Required Remarks User. ID int Primary key Yes Name char(100) Yes Email char(50) No Spot. ID int Foreign key Yes Surrogate key: initial value = 1 Increment = 1 Reference: PARKING 43

IST 210 44 PARKING(Spot. ID, Location, Monthly. Cost) USER(User. ID, Name, Email, Spot. ID) PARKING table Column Name Data Type Key Require d Remarks Spot. ID int Primary key Yes Surrogate key: initial value = 1 Increment =1 Location char(50) Yes Monthly. Cost int Yes Default: 50 USER table Column Name Data Type Key Require d Remarks User. ID int Primary key Yes Surrogate key: initial value = 1 Increment =1 Name char(100) Yes Email char(50) No Spot. ID int Foreign key Yes Reference: PARKING Notes: • See the red parts • Char is not a data type, char(50) is a data type. If you use char, you need to specify the max length • When you can use other data types, do not use char. For example, it is better to use int for Monthly. Cost instead of using char

IST 210 45 In-class Exercise 2 Transform this diagram into tables BUILDING(Building. Name, Address) APARTMENT(Building. Name, Apartment. ID, Number. Of. Bedrooms, Numberof. Baths, Rent)

IST 210 In-class Exercise 2 46 BUILDING(Building. Name, Address) APARTMENT(Building. Name, Apartment. ID, Number. Of. Bedrooms, Numberof. Baths, Rent) BUILDING table Column Name Data Type Key Required Remarks Building. Name Address APARTMENT table Column Name Building. Name Apartment. ID Number. Of. Bed Number. Of. Bath Rent Data Type Key Required Remarks

IST 210 47 BUILDING(Building. Name, Address) APARTMENT(Building. Name, Apartment. ID, Number. Of. Bedrooms, Numberof. Baths, Rent) BUILDING table Column Name Data Type Building. Name Char(20) Address Key Required Primary key Yes Char(100) Yes Remarks Format: street, city, state, zip code APARTMENT table Column Name Data Type Building. Name Required Remarks Char(20) Primary key, foreign key Yes Reference: BUILDING Apartment. ID int Yes Number. Of. Bed float Yes Number. Of. Bath float Yes Rent Yes float Key Primary key

IST 210 48 BUILDING table Column Name Data Type Building. Name Char(20) Address Key Required Primary key Yes Char(100) Yes Remarks Format: street, city, state, zip code APARTMENT table Column Name Data Type Building. Name Required Remarks Char(20) Primary key, foreign key Yes Reference: BUILDING Apartment. ID int Yes Number. Of. Bed float Yes Number. Of. Bath float Yes Rent Yes float Key Primary key Notes: • Apartment. ID should NOT be a surrogate key. In real scenarios, we are using some meaningful Apartment. IDs, such as 100 or 201 instead of a meaningless system-generated id. • Use float for #of. Bed, #of. Bath, and Rent. Because #of. Bath could be 1. 5 and rent could be $890. 50, which are not integers.

IST 210 In-class Exercise 3 Transform this diagram into tables STUDENT(Student. ID, Student. Name, Email) COURSE(Course. ID, Course. Name, Instructor) REGISTRATION(Student. ID, Course. ID) 49

In-class Exercise 3 IST 210 50 STUDENT(Student. ID, Student. Name, Email) COURSE(Course. ID, Course. Name, Instructor) REGISTRATION(Student. ID, Course. ID) STUDENT table Column Name Data Type Key Required Remarks Student. ID Student. Name Email COURSE table Column Name Data Type Key Course. ID Course. Name Instructor REGISTRATION table Column Name Student. ID Course. ID Data Type Key

51 IST 210 STUDENT(Student. ID, Student. Name, Email) COURSE(Course. ID, Course. Name, Instructor) REGISTRATION(Student. ID, Course. ID) STUDENT table Column Name Data Type Key Required Remarks Student. ID int Yes Surrogate key: initial value = 1 Increment =1 Primary key Student. Name Char(100) Yes Email Char(50) No Column Name Data Type Key Required Remarks Course. ID Char(20) Yes Format: Department. Name + Course. Number Course. Name Char(50) Yes Instructor Char(100) No COURSE table Primary key REGISTRATION table Column Name Data Type Key Required Remarks Student. ID int Primary key, foreign key Yes Reference: STUDENT Course. ID Char(20) Primary key, foreign key Yes Reference: COURSE

IST 210 52 STUDENT table Column Name Data Type Key Required Remarks Student. ID int Primary key Yes Surrogate key: initial value = 1 Increment =1 Student. Name Char(100) Yes Email No Char(50) COURSE table Column Name Data Type Key Required Remarks Course. ID Char(20) Primary key Yes Format: Department. Name + Course. Number Course. Name Char(50) Yes Instructor Char(100) No REGISTRATION table Column Name Data Type Key Required Remarks Student. ID int Primary key, foreign key Yes Reference: STUDENT Course. ID Char(20) Primary key, foreign key Yes Reference: COURSE Notes: • A Course. ID should not be integer, for example, “IST 210” is a course. ID. Course. Name for IST 210 is “Organization of the data”.

IST 210 E-R Diagram Tables 1. Entity Table • Entity name Table name • Identifier Primary key • Attributes 2. Relationship Foreign key or a new table 3. Specify the attribute properties 53

IST 210 Attendance check? 54