1 / 51

Database Design and Administration By: Sahar Mosleh

Database Design and Administration By: Sahar Mosleh. Relational Data Model. File Management Systems Before the existence of DBMS, the data were stored in separate files. There was no link from one file to another

thanos
Download Presentation

Database Design and Administration By: Sahar Mosleh

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Design and Administration By: Sahar Mosleh

  2. Relational Data Model

  3. 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

  4. 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

  5. SalesReps • 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 • 106 Sam Clark 52 11 VP Sales 14-JUN-88 275000 299912 • 104 Bob Smith 33 12 Sales Mgr 19-MAY-87 106 200000 142594 • 101 Dan Roberts 45 12 Sales Rep 20-OCT-86 104 300000 305673 • 110 Tom Synder 41 Sales Rep 13-JAN-90 101 75985 • 108 Larry Fitch 62 21 Sales Mgr 12-OCT-89 106 350000 361865 • 103 Paul Cruz 29 12 Sales Rep 01-MAR-87 104 275000 286775 • Nacy Angelli 49 22 Sales Rep 14-NOV-88 108 300000 186042 • …. • …. Orders Order_Num Order_Date Cust Rep MFR Product QTY Amount 112961 17-DEC-89 2117 106 REI 2A44L 7 31500 113012 11-JAN-90 2111 105 ACI 41003 35 3745 112989 03-JAN-90 2101 106 FEA 114 6 1458 113051 10-FEB-90 2118 108 QSA K47 4 1420 112968 12-OCT-89 2102 101 ACI 41004 34 3978 113036 30-JAN-90 2107 110 ACI 4100Z 9 22500 113045 02-FEB-90 2112 108 REI 2A44R 10 45000 112963 17-DEC-89 2103 105 ACI 41004 28 3276 113013 14-JAN-90 2118 108 BIC 41003 1 652 113058 23-FEB-90 2108 109 FEA 112 10 1480 112997 08-JAN-90 2124 107 BIC 41003 1 652 112983 27-DEC-89 2103 105 ACI 41004 6 702 113024 20-JAN-90 2114 108 QSA XK47 20 7100 113062 24-FEB-90 2124 107 FEA 114 10 2430 112979 12-OCT-89 2114 102 ACI 4100Z 6 15000 …… Products Mfr_Id Product_Id Description Price Qty_On_Hand REI 2A45C RATCHET LINK 79 210 ACI 4100Y WIDGET REMOVER 2750 25 QSA XK47 REDUCER 355 38 BIC 41672 PLATE 180 0 IMM 779C 900-LB BRACE 1875 9 ACI 41003 SIZE 3 WIDGET 107 207 ACI 41004 SIZE 4 WIDGET 117 139 BIC 41003 HANDLE 652 3 IMM 887P BRACE PIN 250 24 QSA XK48 REDUCER 134 203 REI 2A44L LEFT HINGE 4500 12 …. Customers Cust_Num Company Cust_Rep Credit_Limit 2111 JCP Inc. 103 50000 2102 First Corp. 101 65000 2103 Acme Mfg. 105 50000 2123 Carter and Sons 102 40000 2107 Ace International 110 35000 2115 Smithson Corp. 101 20000 2101 Jones Mfg. 106 65000 2112 Zetacorp 108 50000 2121 QMA Assoc. 103 45000 2114 Orion Corp. 102 20000 …… Offices Office City Region 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 21 Los Angeles Western 108 725000 835915

  6. 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

  7. 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

  8. 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

  9. 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 2, 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

  10. Example of primary key • Consider the Offices table Office City Region 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 21 Los Angeles Western 108 725000 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.

  11. Consider the Products table Mfr_Id Product_Id Description Price Qty_On_Hand REI 2A45C RATCHET LINK 79 210 ACI 4100Y WIDGET REMOVER 27.50 25 QSA XK47 REDUCER 355 38 BIC 41672 PLATE 180 0 IMM 779C 900-LB BRACE 1875 9 ACI 41003 SIZE 3 WIDGET 107 207 ACI 41004 SIZE 4 WIDGET 117 139 BIC 41003 HANDLE 652 3 IMM 887P BRACE PIN 250 24 QSA XK48 REDUCER 134 203 REI 2A44L LEFT HINGE 4500 12 …. • What is a good primary key for this table?

  12. In this case, MrfId by itself, is not a good choice to be a primary key because more than one manufacturer may produce more than one product • Further, ProductId 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.

  13. Relationship • Consider the following two tables 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 13 Atlanta Eastern 105 350000 367911 21 Los Angeles Western 108 725000 835915 SalesReps 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 106 Sam Clark 52 11 VP Sales 14-JUN-88 275000 299912 …. ….

  14. Relationships are created by having the same data in two or more tables 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 13 Atlanta Eastern 105 350000 367911 21 Los Angeles Western 108 725000 835915 SalesReps 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 106 Sam Clark 52 11 VP Sales 14-JUN-88 275000 299912 …. ….

  15. Note that the RepOffice column in salesreps table contains the office number of the sales office where each sales person works • The values of the RepOffice 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 RepOffice (11) and finding the corresponding row offices table

  16. 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 13 Atlanta Eastern 105 350000 367911 21 Los Angeles Western 108 725000 835915 Foreign key SalesReps Emp_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 106 Sam Clark 52 11 VP Sales 14-JUN-88 275000 299912 …. ….

  17. Example of Foreign Key Customers SalesReps 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 2A45C ….. ACI 4100Y ….. QSA XK47 …… …. ….. Orders Order_Num Order_Date Cust Rep Mfr Product QTY Amount 11296 17-DEC-89 2117 106 REI 2A44L 7 31500 113012 11-JAN-90 2111 105 ACI 41003 35 3745 112989 03-JAN-90 2101 106 FEA 114 6 1458 113051 10-FEB-90 2118 108 QSA K47 4 1420 ….

  18. Entity Relational Diagram (ERD) Modeling

  19. 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, HomePhone • companyName, Headquarters, President

  20. 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, HomePhone • e1: “Bill Adams”, “561 Machray Hall”, “25”, “8831” • e2: “John Smith”, “563 Queen Hall”, “30”, “8833”

  21. Simple and Derived Attributes • Simple attribute • Attributes that are not divisible • Ex: age, SSN, StudentId, 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 BirthDate or • GPA can be derived from Grades • Attribute values can be derived from other entities. • NumberOfEmployees of a department in department entity can be derived by counting the number of employees who work in that department in the employee entity

  22. Null Values • Not Applicable • A particular entity does not have an applicable value for an attribute • ApartmentNumber is null because the family lives in the house and not in an apartment • CollegeDegrees 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) • HomePhone, OfficePhone

  23. 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, ...

  24. ER Notation • Entity • Rectangular box • Enclosing the entity name • Attribute Name • Oval • Attached to its entity by straight line • Composite Attribute • Attached to the component attribute by straight line EMPLOYEE EmpId EMPLOYEE

  25. Attribute Notations Key Attribute Derived Attribute Regular Attribute

  26. 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 Attribute s • 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, StudentId and SSN. However, you only have to pick one as the key (primary key) for the entity • Entities can only have one primary key

  27. Entities SUPPLIER PROJECT WAREHOUSE EMPLOYEE PART LOCATION DEPARTMENT

  28. Supplier No Supplier Name Location Entities and Attributes SUPPLIER PROJECT Project No Project Name Location WAREHOUSE PART EMPLOYEE Wareh. No Wareh. Name Emp. No Emp. Name Part No Part Name Location QTY WGT Addr Title Salary LOCATION DEPARTMENT Dept. No Dept. Name City Manager

  29. All Notations Regular entity Entities Attributes Regular Key Derived Relationships Or

  30. Example of ERD Project Name Employee Name Employee No Duration Project No EMPLOYEE WORKS ON WORKS ON PROJECT Responsibility Title Salary Budget TotalEmp Address Location

  31. 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

  32. 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 Duration Project Name Employee Name Employee No Project No WORKS ON EMPLOYEE WORKS ON PROJECT Title Salary Budget

  33. 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 WORKS-IN EMPLOYEE DEPARTMENT MANAGES

  34. One-to-One Relationship • Each instance of an entity class E1 can be associated with at most one instance of another entity class E2 and vice versa. • Example : • Each employee can work on at most one project and each project employs at most one employee. Project Name Duration Employee Name Employee No Project No 1 1 WORKSON EMPLOYEE WORKS ON PROJECT Title Salary Budget Responsibility

  35. One-to-One Relationship WorksOn Relationship EMPLOYEE PROJECT p1 e1 p2 e2 p3 e3 p4 e4 . . . . . .

  36. One-to-Many relationship • Each instance of one entity class E1 can be associated with zero or more instances of another entity class E2, but each instance of E2 can be associated with at most 1 instance of E1. • Example : • Each employee can work on at most one project; each project can employ manyemployees. Project Name Duration Employee Name Employee No Project No 1 N WORKSON EMPLOYEE WORKS ON PROJECT Title Salary Budget Responsibility

  37. One-to-Many Relationship WorksOn Relationship EMPLOYEE PROJECT p1 e1 p2 e2 p3 e3 p4 e4 . . . e5 . .

  38. 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 Project Name Duration Employee Name Employee No Project No M N WORKSON EMPLOYEE WORKS ON PROJECT Title Salary Budget Responsibility

  39. WorksOn Relationship EMPLOYEE PROJECT p1 e1 p2 e2 p3 e3 p4 e4 . . . e5 . . Many-to-Many Relationship

  40. Multiple Relationships Responsibility Duration WORKS-ON Project Name Employee Name Employee No Project No N M EMPLOYEE PROJECT 1 1 Title Salary Budget MANAGES

  41. 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 (ProjectNo, ProjectName, Budget) PROJECT WORKS ON Budget

  42. Step 2: Binary one-to-one Relationship • For each binary one-to-one relationship: • As you did in step 1, create 2 tables T1 and T2 for entities E1 and E2 that relate to each other by one-to-one relationship. • Choose one table (say T1) and include the primary key of T2 as a foreign key in T1. • Include all the attributes of the relationship as attributes of T1 • In Summary: Create a foreign key and move relationship attributes to any side of the one-to-one relationship

  43. Example of Binary one-to-one Relationship Project Name Duration Employee Name Employee No Project No 1 1 WORKSON EMPLOYEE WORKS ON PROJECT Title Salary Budget Responsibility Foreign key that references Project table Table 1: EMPLOYEE (EmployeeNo, EmployeeName, Title, Salary, ProjectNo, Duration, Responsibility, ) Table 2: PROJECT (ProjectNo, ProjectName, Budget) OR Table 1: PROJECT (ProjectNo, ProjectName, Budget, EmployeeNo, Duration, Responsibility) Table 2: EMPLOYEE (EmployeeNo, EmployeeName, Title, Salary) Foreign key that references Employee table

  44. Another example of Binary one-to-one Relationship Project Name Start-Date Employee Name Employee No Project No 1 1 MANAGES EMPLOYEE WORKS ON PROJECT Title Salary Budget • In this case, because PROJECT has total participation and EMPLOYEE has partial participation, it is a better idea to do the following: • Table 1: PROJECT (ProjectNo, ProjectName, Budget, EmployeeNo, Start-Date) • Table 2: EMPLOYEE (EmployeeNo, EmployeeName, Title, Salary) Foreign key that references Employee table

  45. 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 T1 and T2 for entities E1 and E2 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 T1) • Include the primary key of T2 as foreign key in T1 • Include any simple attributes of the one-to-many relationship as attributes of T1 • Summary: Create a foreign key and move relationship attributes to the N-side of the one-to-many relationship

  46. Example of Binary One-to-Many Relationship Project Name Duration Employee Name Employee No Project No 1 N WORKS ON EMPLOYEE WORKS ON PROJECT Title Salary Budget Responsibility Foreign key that references Project table Table 1: EMPLOYEE (EmployeeNo, EmployeeName, Title, Salary, ProjectNo, Duration, Responsibility) Table 2: PROJECT (ProjectNo, ProjectName, Budget)

  47. Step 4: Binary Many-to-Many Relationship • For each binary many-to-many relationship • As you did in step 1, create 2 tables T1 and T2 for entities E1 and E2 that relate to each other by many-to-many relationship. • Create a new Table T3 • Include as foreign key attributes in T3 the primary keys of T1 and T2. Their combination will form the primary key of T3 • Include any simple attributes of the many-to-many relationship as attributes of T3 • Summary: Each many-to-many relationship becomes a table with foreign keys to the participants

  48. Example of Binary Many-to-Many Relationship Project Name Duration Employee Name Employee No Project No M N WORKS ON EMPLOYEE WORKS ON PROJECT Title Salary Budget Responsibility Table 1: EMPLOYEE (EmployeeNo, EmployeeName, Title, Salary) Table 2: PROJECT (ProjectNo, ProjectName, Budget) Table 3: WORKS-ON (EmployeeNo, ProjectNo, Duration, Responsibility) Foreign key that references Project table Foreign key that references Employee table

  49. 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

  50. Example: • Given the following ERD find the corresponding tables. Flight: FlightNo, Destination Passenger: PassNo, PassName, PassAddr Fly: FlightNo, PassNo, SeatNo, Meal Baggage: BagNo, Weight, PassNo

More Related