Database Design and Administration By Sahar Mosleh California
Database Design and Administration By: Sahar Mosleh California State University San Marcos Page 1
Relational Data Model Sahar Mosleh California State University San Marcos Page 2
File Management Systems • Before the existence of DBMS, the data were stored in separate files. • There was no link from one file to another • If the structure of the data changed (ex: adding more fields), programs that were using the file had to change • Problems became more severe when the number of the programs using the files increased over time Sahar Mosleh California State University San Marcos Page 3
Relational Data Model Definition: • A relational database is a database where all data visible to users is organized strictly as tables of data values and where all database operations work on these tables • In this model information is stored in a database as simple row/column tables of data • Next slide shows an example of tables in a relational database Sahar Mosleh California State University San Marcos Page 4
Sales. Reps Empl_Num Name Age Rep_Office Title Hire_Date Manager Quota Sales 105 Bill Adams 37 13 Sales Rep 12 -FEB-88 104 350000 367911 109 Mary Jones 31 11 Sales Rep 12 -OCT-89 106 300000 392725 102 Sue Smith 48 21 Sales Rep 10 -DEC-86 108 350000 474050 Orders 106 Sam Clark 52 11 VP Sales 14 -JUN-88 275000 299912 104 Bob Smith 33 12 Sales Mgr 19 -MAY-87 142594 Order_Num Order_Date 106 Cust 200000 Rep MFR Product QTY Amount 101 Dan Roberts 45 12 Sales Rep 20 -OCT-86 112961 17 -DEC-89 104 2117 300000 106 REI 305673 2 A 44 L 7 31500 Products 113012 11 -JAN-90 101 2111 105 ACI 75985 41003 35 3745 110 Tom Synder 41 Sales Rep 13 -JAN-90 112989 03 -JAN-90 2101 350000 106 FEA 114 6 1458 Product_Id Price Qty_On_Hand 108 Mfr_Id Larry Fitch Description 62 21 Sales Mgr 12 -OCT-89 106 361865 113051 10 -FEB-90 2118 108 QSA K 47 1420 2 A 45 C RATCHET 210 104 103 REI Paul Cruz 29 12 LINK Sales Rep 7901 -MAR-87 275000 286775 4 112968 12 -OCT-89 2102 101 ACI 41004 34 3978 ACI 4100 Y 25 1 Nacy Angelli WIDGET 49 22 REMOVER Sales 113036 Rep 2750 14 -NOV-88 108 300000 186042 Customers 30 -JAN-90 2107 110 ACI 4100 Z 9 22500 REDUCER 355 38 …. QSA XK 47 113045 02 -FEB-90 2112 108 REI 2 A 44 R 10 45000 Cust_Num Company Cust_Rep Credit_Limit 41672 PLATE 18017 -DEC-89 0 …. BIC 112963 2103 105 ACI 41004 28 3276 2111 JCP Inc. 103 50000 IMM 779 C 900 -LB BRACE 113013 187514 -JAN-90 9 2118 108 BIC 41003 1 652 2102 First Corp. 101 65000 2108 109 FEA 112 10 1480 ACI 41003 SIZE 3 WIDGET 113058 10723 -FEB-90 207 Acme Mfg. 139 2124 105107 BIC 41003 50000 1 112997 652 ACI 41004 SIZE 4 WIDGET 2103 11708 -JAN-90 112983 41004 6 702 2123 Carter and 3 Sons 2103 102105 ACI 40000 BIC 41003 HANDLE 65227 -DEC-89 Offices 113024 20 -JAN-90 2114 110 108 QSA 35000 XK 47 20 7100 2107 Ace International IMM 887 P BRACE PIN 250 24 113062 24 -FEB-90 2124 101 107 FEA 20000 114 10 2430 Smithson Corp. Region 2115 Mgr Target Sales QSA Office XK 48 City REDUCER 134 203 112979 12 -OCT-89 2114 102 ACI 4100 Z 6 15000 Jones Mfg. 12 106 65000 Western 2101 108 300000 186042 REI 22 2 A 44 L Denver LEFT HINGE 4500 …… 2112 108 50000 11 New York Eastern 106 Zetacorp 575000 692637 …. Assoc. 735042 103 45000 12 Chicago Eastern 2121 104 QMA 800000 Corp. 367911 102 20000 13 Atlanta Eastern 2114 105 Orion 350000 21 Los Angeles Western…… 108 725000 835915 Sahar Mosleh California State University San Marcos Page 5
Table Definition: • A table is a rectangular object with rows and columns • For example in the office table: • Each row of the office table represents a single physical entity • Each column of the offices table represents one item of data that is stored in the database for each office: • Ex: City column represents the location of the office • An alternative term for column is attribute • Each row of the table contains exactly one data value in each column Sahar Mosleh California State University San Marcos Page 6
• In each column of a table, all of the data values in that column have the same type. For example: • City column values are words • Sales values are money type • Mgr values are integer • Each column in a table has a column name which is written as a heading at the top of the column • Column names must be unique in a table • The columns of a table have a left-right order. That is defined when the table is first created. • The order of the column has no effect when any action is done against the table Sahar Mosleh California State University San Marcos Page 7
• Each table must have at least one column • Almost all commercial DBMS products impose maximum of 255 columns per table • A table can have zero or more rows • A table with zero rows is called an empty table • Order of the rows is not important in a table. • Most relational DBMSs either do not impose any limit on the number of rows or their limit is a very large number • A common limit is approximately 2 billion rows Sahar Mosleh California State University San Marcos Page 8
Primary Key Definition: • A primary key is a column or combination of two or more columns that uniquely identifies each row of a table. • Since the order of rows in a table is irrelevant, rows cannot be identified based on their positions in a table • Ex: row 1, row 20 • In a well-designed relational database each table has a primary key. • If the primary key contains two or more columns, it is called a composite primary key Sahar Mosleh California State University San Marcos Page 9
Example of primary key • Consider the Offices table Office 22 11 12 13 21 City Denver New York Chicago Atlanta Los Angeles Region Western Eastern Western Mgr 108 106 104 105 108 Target 300000 575000 800000 350000 725000 Sales 186042 692637 735042 367911 835915 • “Office” column (attribute) can be a good choice for the primary key because each office has a different office id • However, city is not a good choice because more than one office may be located in the same city. Sahar Mosleh California State University San Marcos Page 10
• Consider the Products table Mfr_Id Product_Id Description REI 2 A 45 C RATCHET LINK ACI 4100 Y WIDGET REMOVER QSA XK 47 REDUCER BIC 41672 PLATE IMM 779 C 900 -LB BRACE ACI 41003 SIZE 3 WIDGET ACI 41004 SIZE 4 WIDGET BIC 41003 HANDLE IMM 887 P BRACE PIN QSA XK 48 REDUCER REI 2 A 44 L LEFT HINGE …. Price 79 27. 50 355 180 1875 107 117 652 250 134 4500 Qty_On_Hand 210 25 38 0 9 207 139 3 24 203 12 • What is a good primary key for this table? Sahar Mosleh California State University San Marcos Page 11
• In this case, Mrf. Id by itself, is not a good choice to be a primary key because more than one manufacturer may produce more than one product • Further, Product. Id by itself is not a good choice either because the same product can be produced by more than one manufacturer. • However, combination of both is unique in every row. • This is an example of composite primary key. • A table with a primary key is called a relation. A relation is a table in which no duplicate rows can exist. Sahar Mosleh California State University San Marcos Page 12
Relationship • Consider the following two tables Offices Sales. Reps Empl_Num Name 105 Bill Adams 109 Mary Jones 102 Sue Smith 106 Sam Clark …. …. Sahar Mosleh Office City Regin Mgr Target Sales 22 Denver Western 108 300000 186042 11 New York Eastern 106 575000 692637 12 Chicago Eastern 104 800000 735042 13 Atlanta Eastern 105 350000 367911 Age Rep_Office Title Manager Quota 21 Hire_Date Los Angeles Western 108 Sales 725000 835915 37 13 Sales Rep 12 -FEB-88 104 350000 367911 31 11 Sales Rep 12 -OCT-89 106 300000 392725 48 21 Sales Rep 10 -DEC-86 108 350000 474050 52 11 VP Sales 14 -JUN-88 275000 299912 California State University San Marcos Page 13
• Relationships are created by having the same data in two or more tables Offices Sales. Reps Empl_Num Name 105 Bill Adams 109 Mary Jones 102 Sue Smith 106 Sam Clark …. …. Sahar Mosleh Office City Regin Mgr Target Sales 22 Denver Western 108 300000 186042 11 New York Eastern 106 575000 692637 12 Chicago Eastern 104 800000 735042 Age Rep_Office Title Hire_Date Manager Quota Sales 367911 13 Atlanta Eastern 105 350000 37 13 Sales 350000 21 Rep 12 -FEB-88 Los Angeles 104 Western 108 367911 725000 835915 31 11 Sales Rep 12 -OCT-89 106 300000 392725 48 21 Sales Rep 10 -DEC-86 108 350000 474050 52 11 VP Sales 14 -JUN-88 275000 299912 California State University San Marcos Page 14
• Note that the Rep. Office column in salesreps table contains the office number of the sales office where each sales person works • The values of the Rep. Office column is the set of office numbers found in the office column of the offices table • We will see how this restriction is imposed when we discuss about creating tables later in the course • For example, it is possible to find the sales office where “Mary Jones” is working by finding the value of Mary Jones Rep. Office (11) and finding the corresponding row offices table Sahar Mosleh California State University San Marcos Page 15
Foreign Key Definition: • Foreign key is a column (or combination or two or more columns) whose value matches the primary key of another table or possibly the same table • Together, primary key and the foreign key make the relationship in relational data models Primary key Offices Office City Regin Mgr Target Sales 22 Denver Western 108 300000 186042 11 New York Eastern 106 575000 692637 12 Chicago Eastern 104 800000 735042 Age Rep_Office Title Hire_Date Manager Quota 13 Atlanta Eastern 105 Sales 350000 367911 37 13 Sales Rep 104 Western 350000108367911 21 12 -FEB-88 Los Angeles 725000 835915 31 11 Sales Rep 12 -OCT-89 106 300000 392725 48 21 Sales Rep 10 -DEC-86 108 350000 474050 52 11 VP Sales 14 -JUN-88 275000 299912 Foreign key Sales. Reps Emp_Num Name 105 Bill Adams 109 Mary Jones 102 Sue Smith 106 Sam Clark …. …. Sahar Mosleh California State University San Marcos Page 16
Example of Foreign Key Customers Sales. Reps Products Cust_Num 2111 …. . 2102 …. 2103 …. ……. Empl_Num Name 105 Bill Adams 109 Mary Jones 102 Sue Smith 106 Sam Clark …. Mfr_Id Product_Id REI 2 A 45 C …. . ACI 4100 Y …. . QSA XK 47 …… …. …. . Orders Order_Num 11296 113012 112989 113051 …. Sahar Mosleh Order_Date Cust Rep Mfr Product QTY 17 -DEC-89 2117 106 REI 2 A 44 L 7 11 -JAN-90 2111 105 ACI 41003 35 03 -JAN-90 2101 106 FEA 114 6 10 -FEB-90 2118 108 QSA K 47 4 California State University San Marcos Amount 31500 3745 1458 1420 Page 17
Entity Relational Diagram (ERD) Modeling Sahar Mosleh California State University San Marcos Page 18
ERD Model • Entity-Relationship, ER Model • ER model describes data as entities, relationships and attributes • Entity • A “thing” in the real world with an independent existence • Physical existence • Person, car, house or employee • Conceptual existence • Company, job, university course • Attribute • Property that describes entity • employee. Name, Address, Age, Home. Phone • company. Name, Headquarters, President Sahar Mosleh California State University San Marcos Page 19
• Value • A particular entity will have a value for each of its attributes • The attribute values that describe each entity become a major part of the stored data • Example • employee: Name, Address, Age, Home. Phone • e 1: “Bill Adams”, “ 561 Machray Hall”, “ 25”, “ 8831” • e 2: “John Smith”, “ 563 Queen Hall”, “ 30”, “ 8833” Sahar Mosleh California State University San Marcos Page 20
Simple and Derived Attributes • Simple attribute • Attributes that are not divisible • Ex: age, SSN, Student. Id, etc • Derived • Attributes that can be derived from other attributes either from the same entity or other entities. For example: • Age can be derived from Birth. Date or • GPA can be derived from Grades • Attribute values can be derived from other entities. • Number. Of. Employees of a department in department entity can be derived by counting the number of employees who work in that department in the employee entity Sahar Mosleh California State University San Marcos Page 21
Null Values • Not Applicable • A particular entity does not have an applicable value for an attribute • Apartment. Number is null because the family lives in the house and not in an apartment • College. Degrees is null because the person never gone to the college • Unknown • Missing (value exists but not known) • Height of a person • Not Known (existence is not sure) • Home. Phone, Office. Phone Sahar Mosleh California State University San Marcos Page 22
Entity • An entity defines a collection object that have the same attributes • Entity is described by its name and attributes • Example: • Employee [Number, Name, Title, Salary] • Project [Number, Budget, Location] • Entity instances are instantiations of the entity • Example: • Employee Joe, Jim, . . . • Project Compiler design, Accounting, . . . Sahar Mosleh California State University San Marcos Page 23
ER Notation • Entity • Rectangular box • Enclosing the entity name EMPLOYEE • Attribute Name • Oval • Attached to its entity by straight line • Composite Attribute • Attached to the component attribute by straight line Emp. Id EMPLOYEE Sahar Mosleh California State University San Marcos Page 24
Attribute Notations Regular Attribute Sahar Mosleh Derived Attribute Key Attribute California State University San Marcos Page 25
• Key Attribute • Values can be used to identify each entity uniquely. This plays the role of primary key in a table • The key attribute is underlined in ER diagram • Composite key Attributes • Combination of the two or more attributes must be distinct for each instance. This plays the role of composite primary key in a table • Entities may have more than one attribute that can be chosen as key attribute. For example, Student. Id and SSN. However, you only have to pick one as the key (primary key) for the entity • Entities can only have one primary key Sahar Mosleh California State University San Marcos Page 26
Entities SUPPLIER WAREHOUSE LOCATION Sahar Mosleh PROJECT EMPLOYEE PART DEPARTMENT California State University San Marcos Page 27
Entities and Attributes SUPPLIER Supplier No Name PROJECT WAREHOUSE Wareh. No Wareh. Location Name LOCATION City Sahar Mosleh Project No Location PART Part No Part Name Project Name Location EMPLOYEE QTY WGT Emp. Addr Title Salary No Name DEPARTMENT Dept. No California State University San Marcos Dept. Name Manager Page 28
All Notations Regular entity Entities Attributes Regular Key Derived Relationships Or Sahar Mosleh California State University San Marcos Page 29
Example of ERD Employee No Employee Name EMPLOYEE Title Sahar Mosleh WORKSON WORKS ON Salary Address Duration Responsibility Project No Project Name PROJECT Budget Total. Emp Location California State University San Marcos Page 30
Relationships • A relationship logically associates two or more entities with each other • Binary relationship associates two entities • Ternary relationship associates three entities with each other • N-ary relationship associates n entities with each other Sahar Mosleh California State University San Marcos Page 31
Relationship attributes • Attributes can be attached to specific relationships. • Attributes that belong to two different entities should be placed as the attribute of the relationship • These values provide data about the relationship between the participating entity instances. Responsibility Employee Name Employee No Title Sahar Mosleh Project Name Project No WORKS ON ON EMPLOYEE Duration PROJECT Salary California State University San Marcos Budget Page 32
Kinds of Relationships (Cardinality) • Fundamental ones are: • One-to-one • Many-to-one (one-to-many) • Many-to-many • NOTE: There can be multiple relationships between two entities EMPLOYEE WORKS-IN DEPARTMENT MANAGES Sahar Mosleh California State University San Marcos Page 33
One-to-One Relationship • Each instance of an entity class E 1 can be associated with at most one instance of another entity class E 2 and vice versa. • Example : • Each employee can work on at most one project and each project employs at most one employee. Employee Name Employee No EMPLOYEE Title Sahar Mosleh 1 Salary Duration WORKS ON ON Project Name Project No 1 Responsibility California State University San Marcos PROJECT Budget Page 34
One-to-One Relationship EMPLOYEE Works. On Relationship PROJECT e 1 p 1 e 2 p 2 e 3 p 3 e 4 p 4 . . . Sahar Mosleh . . . California State University San Marcos Page 35
One-to-Many relationship • Each instance of one entity class E 1 can be associated with zero or more instances of another entity class E 2, but each instance of E 2 can be associated with at most 1 instance of E 1. • Example : • Each employee can work on at most one project; each project can employ many employees. Employee Name Employee No EMPLOYEE Title Sahar Mosleh N Salary Duration WORKS ON ON Project Name Project No 1 Responsibility California State University San Marcos PROJECT Budget Page 36
One-to-Many Relationship EMPLOYEE Works. On Relationship PROJECT e 1 p 1 e 2 p 2 e 3 p 3 e 4 p 4 . . . e 5 . . Sahar Mosleh California State University San Marcos Page 37
Many-to-Many Relationship • Each instance of one entity class can be associated with many instances of another entity class, and vice versa. • Example : • Each employee can work on many projects; each project can employ many employees Employee Name Employee No EMPLOYEE Title Sahar Mosleh N Salary Duration WORKS ON ON Project Name Project No M Responsibility California State University San Marcos PROJECT Budget Page 38
Many-to-Many Relationship EMPLOYEE Works. On Relationship PROJECT e 1 p 1 e 2 p 2 e 3 p 3 e 4 p 4 . . . e 5 . . Sahar Mosleh California State University San Marcos Page 39
Multiple Relationships Duration Employee No Employee Name Responsibility WORKS-ON N Project No M EMPLOYEE PROJECT 1 Title Project Name 1 Salary Budget MANAGES Sahar Mosleh California State University San Marcos Page 40
Mapping ERD to Table: Step 1: • For each entity E in the ERD • Create a table T that includes all the simple attributes of E • Choose one of the key attributes of E as primary key for T • If the chosen key of E is composite, the set of simple attributes that form it will together form the primary key of T Example: Project Name Project No Project (Project. No, Project. Name, Budget) PROJECT WORKS ON Budget Sahar Mosleh California State University San Marcos Page 41
Step 2: Binary one-to-one Relationship • For each binary one-to-one relationship: • As you did in step 1, create 2 tables T 1 and T 2 for entities E 1 and E 2 that relate to each other by one-to-one relationship. • Choose one table (say T 1) and include the primary key of T 2 as a foreign key in T 1. • Include all the attributes of the relationship as attributes of T 1 • In Summary: Create a foreign key and move relationship attributes to any side of the one-to-one relationship Sahar Mosleh California State University San Marcos Page 42
Example of Binary one-to-one Relationship Employee Name Employee No EMPLOYEE Title 1 Salary Duration WORKS ON ON Project Name Project No 1 PROJECT Responsibility Budget Foreign key that references Project table Table 1: Table 2: OR Table 1: Table 2: Sahar Mosleh EMPLOYEE (Employee. No, Employee. Name, Title, Salary, Project. No, Duration, Responsibility, ) PROJECT (Project. No, Project. Name, Budget) Foreign key that references Employee table PROJECT (Project. No, Project. Name, Budget, Employee. No, Duration, Responsibility) EMPLOYEE (Employee. No, Employee. Name, Title, Salary) California State University San Marcos Page 43
Another example of Binary one-to-one Relationship Employee Name Employee No EMPLOYEE Title 1 Start-Date MANAGES WORKS ON Project Name Project No 1 PROJECT Salary Budget • In this case, because PROJECT has total participation and EMPLOYEE has partial participation, it is a better idea to do the following: Foreign key that references Employee table Table 1: PROJECT (Project. No, Project. Name, Budget, Employee. No, Start-Date) Table 2: EMPLOYEE (Employee. No, Employee. Name, Title, Salary) Sahar Mosleh California State University San Marcos Page 44
Step 3: Binary One-to-Many Relationship • For each regular binary one-to-many relationship do: • As you did in step 1, create 2 tables T 1 and T 2 for entities E 1 and E 2 that relate to each other by one-to-Many relationship. • Identify the table that represents the participating entity at the N-side (say this table is T 1) • Include the primary key of T 2 as foreign key in T 1 • Include any simple attributes of the one-to-many relationship as attributes of T 1 • Summary: Create a foreign key and move relationship attributes to the N-side of the one-to-many relationship Sahar Mosleh California State University San Marcos Page 45
Example of Binary One-to-Many Relationship Employee Name Employee No EMPLOYEE Title N Salary Duration WORKS ON ON Project Name Project No 1 Responsibility PROJECT Budget Foreign key that references Project table Table 1: EMPLOYEE (Employee. No, Employee. Name, Title, Salary, Project. No, Duration, Responsibility) Table 2: PROJECT (Project. No, Project. Name, Budget) Sahar Mosleh California State University San Marcos Page 46
Step 4: Binary Many-to-Many Relationship • For each binary many-to-many relationship • As you did in step 1, create 2 tables T 1 and T 2 for entities E 1 and E 2 that relate to each other by many-to-many relationship. • Create a new Table T 3 • Include as foreign key attributes in T 3 the primary keys of T 1 and T 2. Their combination will form the primary key of T 3 • Include any simple attributes of the many-to-many relationship as attributes of T 3 • Summary: Each many-to-many relationship becomes a table with foreign keys to the participants Sahar Mosleh California State University San Marcos Page 47
Example of Binary Many-to-Many Relationship Employee Name Employee No EMPLOYEE Title Table 1: Table 2: Table 3: N Salary Duration WORKS ON ON Project Name Project No M Responsibility PROJECT Budget EMPLOYEE (Employee. No, Employee. Name, Title, Salary) PROJECT (Project. No, Project. Name, Budget) WORKS-ON (Employee. No, Project. No, Duration, Responsibility) Foreign key that references Project table Foreign key that references Employee table Sahar Mosleh California State University San Marcos Page 48
Some Comments • When you create your ERD, it is a better idea to follow the following general rules • You should use the convention that entity type and relationship type names are in uppercase letters, attribute names are capitalized and role names are in lowercase letters • Another naming consideration involves choosing binary relationships names to make the ERD of the schema readable from left to right and from top to bottom Sahar Mosleh California State University San Marcos Page 49
Example: • Given the following ERD find the corresponding tables. Flight: Passenger: Fly: Baggage: Sahar Mosleh Flight. No, Destination Pass. No, Pass. Name, Pass. Addr Flight. No, Pass. No, Seat. No, Meal Bag. No, Weight, Pass. No California State University San Marcos Page 50
• Part of the Lab 1 Question: Given the following ERD, find the corresponding tables. Fac-id Dep-Location N Faculty Dep-Name Dep-id Fac-Name 1 Belongs-to 1 M N Department offers Can-take teach 1 gives N M Student St-id Sahar Mosleh M St-Name take St-email N N 1 Course semester California State University San Marcos Major Maj-id Maj-Name Page 51
- Slides: 51