1 / 47

Review Lab 5 ERD’s

Review Lab 5 ERD’s. DBS201. How do you get to this screen in SQL?. The following statements are run. Set schema PRMIERC40 Select * From Customer Select * from Salesrep Select * from Cust_Rep. Frank Soltis is a Customer with salesrep 99. SalesRep 99 is Russell Pangborn.

hedy-anthony
Download Presentation

Review Lab 5 ERD’s

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. ReviewLab 5ERD’s DBS201

  2. How do you get to this screen in SQL?

  3. The following statements are run • Set schema PRMIERC40 • Select * From Customer • Select * from Salesrep • Select * from Cust_Rep

  4. Frank Soltis is a Customer with salesrep 99

  5. SalesRep 99 is Russell Pangborn

  6. Where are Frank Soltis and Russ Pangborn

  7. Anything wrong? create View PRMIERC40.CUST_REPas ( SELECT C.CUSTOMER_NUMBER, TRIM(C.FIRST_NAME) || ' ' || C.LAST_NAME AS CUST_NAME, TRIM(S.FIRST_NAME) || ' ' || S.LAST_NAME AS REP_NAMEFROM PREMIERE.CUSTOMER C, PREMIERE.SALESREP S WHERE C.SALES_REP_NUMBER = S.SALES_REP_NUMBER )

  8. Duplicate key value specified. INSERT INTO PRMIERC40.CUSTOMER (SELECT * FROM PREMIERE.CUSTOMER)

  9. SELECT CUSTOMER_NUMBER, LAST_NAME, FIRST_NAME, STREET, CITY, STATE, BALANCE, SALES_REP_NUMBER FROM PREMIERE.CUSTOMER

  10. What command is being used? • DSPFDPRMIERC40/CUSTOMER *CST

  11. LAB 5

  12. Entity Relationship DiagramsERDs DBS201

  13. Why ERDs • Documentation used to represent the database in an abstract way • The data model can be reviewed by the end user and the person responsible for the physical database design • Useful tool for the person creating the data model.

  14. What is an ERD • This conceptual data model represents the data used in an organization and the relationships between the data • It is a graphical representation of the proposed database

  15. Entities and Events • Entities - People, places, things or concepts about which information must be recorded • Entities as Events – Placing an order or approving a loan • Attributes for entities, events and relationships would be things like customer names or dates on which orders were placed • Attribute – one single valued fact about an entity that we may want to record

  16. E (Relationship)Ds • Relationships are found between entities • An employee is in a department • A department has many employees • Business rules must be taken into account • Every employee must be in a single department

  17. E (Relationship)Ds • Relationships are found between entities and events • A customer (entity) places an order (event) • A loan officer (entity) approves a loan (event) • Business rules must be taken into account • Loan example - a rule that the borrower must have an adjusted gross income of at least half of his or her outstanding debt may be enforced

  18. An ERD should • capture all required information • make sure data appears only once in the database design • not include in the data model any data that is derived from other data that is already in the data model • arrange data in the data model in a logical manner

  19. Equivalent Terms:

  20. Customer • Customer Relation • Customer Table • Customer File • Customer Entity

  21. Last Name • Last name attribute in Customer Relation • Last name column in Customer Table • Last name field in Customer File • Last name property of Customer Entity

  22. Phone Number • Customer Relation phone number domain is numeric or character • Customer Table phone number column type is numeric or character • Customer File phone number data type is numeric or character • Customer Entity phone number allowable values are numeric or character

  23. Phone Number is 4164915050 • Customer Relation phone number element is 4164915050 or (416) 491-5050 • Customer Table phone number column value is 4164915050 or (416) 491-5050 • Customer Table phone number field value is 4164915050 or (416) 491-5050 • Customer Table phone number property value is 4164915050 or (416) 491-5050 • A numeric field can used an edit code to get the special characters included “() – ”

  24. Last Name, First Name, Phone • Smith, Bill, 9056668888 as a tuple in the Customer Relation • Smith, Bill, 9056668888 as a row in the Customer Table • Smith, Bill, 9056668888 as a record in the Customer File • Smith, Bill, 9056668888 as an entity instance of the Customer Entity

  25. Steps in Designing an ERD • Create Entities by identifying the people, places or events about which the end user wants to store data • Define Attributes by determining the attributes that are essential to the system under development. For each attribute, match it with exactly one entity that it describes

  26. Steps in Designing an ERD • Select Unique Identifier Identify the data attribute(s) that uniquely identify one and only one occurrence of each entity. Eliminate many to many relationships, and include a unique identifier (UID) and foreign keys in each entity • Define Relationships Find the natural associations between pairs of entities using a relationship matrix. Arrange entities in rectangles and join those entities with a line

  27. Steps in Designing an ERD • Determine Optionality and CardinalityDetermine the number of occurrences of one entity for a single occurrence of the related entity. • Name Relationships Name each relationship between entities • Eliminate Many-toMany Relationships Many-to-many relationships cannot be implemented into database tables because each row will need an indefinite number of attributes to maintain the many-to-many relationship. Many-to-many relationships must be converted to one-to-many relationships using associative entities • Determine Data Types Identify the data types and sizes of each attribute

  28. Case Study • Each employee may be assigned to one and only one department. Some employees may not be assigned to any department. The employee data is stored in the employee entity. • Each department could have many employees assigned to it. Some departments nay not have any employees assigned to them. The department data is stored in the department entity. • Each employee may have one and only one job title. Under certain circumstances, some employees may not be assigned a job title.

  29. Case Study • Each job title may be assigned to many employees. Some job titles may not be assigned to any employees. The job data is stored in the job entity. • Each employee may be assigned to many projects. Sometimes an employee may be off work and is not assigned to any projects. • Each project must be assigned to at least one employee. Some projects may have several employees assigned to them. The project data is stored in the project entity

  30. Identify Entities • Employee • Department • Job • Project

  31. Identify Attributes • Employee – employee_id, first_name, last_name, soc_ins_no, hire_date • Volatile attributes • Required and Optional Attributes • Time dependant attributes • Domains

  32. Select Unique Identifier (UID) • Every entity must have unique identifying attribute(s) called a unique identifier • This is a single attribute or a collection of attributes that uniquely identifies one and only one instance of an entity. • When two or more attributes are used as the unique identifier it is called a concatenated key

  33. Candidate Key • Sometimes there are several attributes that could be the unique identifier • For an EMPLOYEE entity we could use employee_id, social_ins_no, email_address or telephone_no • These are all called candidate keys

  34. Candidate Keys Must: • be unique for each instance within an entity • never be missing, incomplete or NULL for an instance • use no attributes other than those necessary to uniquely identify an instance of an entity

  35. A Unique Identifier: • should be meaningless other than as an identifier • should never change • should not have a limited number of values available • only one (UID) should be specified for each table

  36. For EMPLOYEE • Soc_ins_no is not meaningless – do you want people knowing your personal number in the company? • A telephone number may change • The best choice is an arbitrarily generated employee number assigned when the employee is hired

  37. Determining relationships A relationship is like a verb that shows some dependency or natural association between two entities DEPARTMENT EMPLOYEE A department contains employees An employee is assigned to a department

  38. Determining optionality and cardinality Each instance of Table B is related to a maximum of one and a minimum of one instance of Table A TABLE A TABLE B

  39. Each instance of Table A is related to zero, one or more instances of Table B TABLE A TABLE B

  40. An employee must be in one and only one department EMPLOYEE PKemployee_idfirst_namelast_namesoc_sec_nohire_datejob_id FK1department_code PKdepartment_codedepartment_namemanager_id DEPARTMENT

  41. A department may have zero, one or many employees EMPLOYEE PKemployee_idfirst_namelast_namesoc_sec_nohire_datejob_id FK1department_code PKdepartment_codedepartment_namemanager_id DEPARTMENT

  42. An employee may be in zero or one department EMPLOYEE PKemployee_idfirst_namelast_namesoc_sec_nohire_datejob_id FK1department_code PKdepartment_codedepartment_namemanager_id DEPARTMENT

  43. Different notations are used to represent the cardinality of relationships • 1:1 • 1:M • M:N • Which one was shown with more detail in the previous examples?

  44. No information on optionality is given with this 1:M example EMPLOYEE PKemployee_idfirst_namelast_namesoc_sec_nohire_datejob_id FK1department_code PKdepartment_codedepartment_namemanager_id DEPARTMENT M 1

  45. Summary • Entity or Event • Relationship • Optionality • Cardinality • Attributes • UID • ERD Diagrams

More Related