Week 5 LECTURE.pdf

BUSA1000 Introduction to Data, Analytics and People Week 5: Data Process 2 Week 4 Recap: any takeaways? BPMN Extension 1. What does a database contain? 2. Database development process 3. Chen’s notation fo...

BUSA1000 Introduction to Data, Analytics and People Week 5: Data Process 2 Week 4 Recap: any takeaways? BPMN Extension 1. What does a database contain? 2. Database development process 3. Chen’s notation for ERD Department of Actuarial Studies and Business Analytics| Business School 2 Overview: Copyright © 2014 Pearson Australia (a division of Pearson Australia Group Pty Ltd) – 9781442561779/Kroenke/Experiencing MIS/3e The user has to solve This software This software Relevant information a problem using component component organises is stored in orderly data stored in the allows the user to the access to the fashion in tables in DB (e.g. Check analyse, organise, database: read, write, the schedule of classes present, print,x delete, change of data database. for the semester). and display data. (e.g., Oracle) 1. What does a Database contain? TABLES Represent business objects E.g., Student, Customer, Employee, Product, etc. Hierarchy of data elements Bytes/characters are grouped into columns/fields Columns/fields grouped into rows/records Rows/records are grouped into tables/files Entity Relationship Diagram (ERD) Several notations exist for data models (our Textbook uses a different notation) We use Chen’s notation because it is relatively easy to understand and use. Entity and attribute notations Name of ENTITY is placed inside a Name of ATTRIBUTE is placed inside an rectangle ellipse Name is stated in singular and in capital Capitalise the first letter of each word letters Where attribute name is two words, use an A noun underscore between words Where an attribute is the identifier (primary key) for the entity it is underlined A noun CONTRACTOR Contractor_ID Contractor_Name Basic ERD concepts: Relationship Relationship Definition Example (MRV) Relationship An association between one or more CONTRACTOR (type) entities operates TOUR à Link between two tables! Relationship Association between entity instances Tony Chan instance operates April Murray River Tour à Link between two table entries! We represent a RELATIONSHIP using a Diamond, with continuous lines connecting it to entities operates Verb! Defining and representing Cardinality Cardinality: A relationship between entities will be one of the following types One to many (1:M) § A many to one (M:1) relationship is the same as a one to many (1:M), only stated in reverse Many to many (M:M) One to One (1:1) (we don‘t model this in INFS1000) § E.g. One customer is allowed to store the details of one of his/her driver’s license § We will treat one to one (1:1) relationships as one to many (1:M) relationships in this course One to Many (1:M) Relationship: An Example “A Department can hire many Advisors Each Advisor is hired by one Department.” DEPARTMENT 1 M ADVISER employs 1:M Relationship – “Tables behind the boxes” DEPARTMENT 1 M ADVISER employs Identify the keys Primary Keys ADVISER AID Name DID DEPARTMENT 001 Jones A01 DID Dep. Name Location 002 Wu A01 A01 Accounting H69 003 Smith A02 A02 Finance M4 004 Lopez A01 Golden Rule for modelling relationships 005 Greene A02 The PK of the entity on the 1-side of the relationship is ALWAYS the FK to the entity on the M-side of the relationship Foreign Key PK of DEPARTMENT (= DID) is added as FK to ADVISER Many to Many (M:M) Relationship: An example Many to Many (M:M) Relationship: An example ADVISER M M STUDENT advises Why not?: Another Possibility Primary keys have to be unique! PKs are NOT unique ADVISER STUDENT AID Name SID SID Name AID 001 Jones 100 100 Miller 001 001 Jones 400 100 Miller 003 003 Smith 100 300 Shang 004 004 Lopez 100 400 Lee 001 005 Greene 700 500 Li 005 600 Ali 004 700 Watson 002 800 Cheng 001 Many to Many (M:M) Relationship: An example Rule for modelling M:M Relationship Associative Entity ADVISER-STUDENT 1 M M 1 ADVISER advises STUDENT The Associative Entity Links two entities and contains attributes unique to their relationship. Always has a composite primary key (in BUSA1000) Combination of the primary keys of the linked entities (e.g. SID + MID) Normally named as combination of the entity names Retain the verb of the relationship and then write the new name in capital letters above or below the box. Sometimes has a unique name (e.g. TRANSACTION, OFFER) OFFER 1 M M 1 SUPPLIER delivers MATERIAL SID Name Address SID MID Price MID Name The final ERD for this example (without attributes) 1 M M 1 ADVISER advises STUDENT M ADVISER-STUDENT employs 1 DEPARTMENT How many tables will the database contain? Which ones? The “tables behind the boxes” 1 M M 1 ADVISER advises STUDENT M ADVISER-STUDENT ADVISER- ADVISER STUDENT STUDENT employs AID Name DID SID Name Grade AID SID 001 Jones A01 100 Miller HD 001 100 002 Wu A01 200 Schulz P 001 400 1 003 Smith A02 300 Shang AF 002 200 004 Lopez A01 400 Lee D 002 600 DEPARTMENT 005 Greene A02 500 Li D 003 100 600 Ali P 003 300 700 Watson F 005 400 DEPARTMENT 800 Cheng D 005 700 DID Dep. Name Location 005 800 Primary Keys A01 Accounting H69 Composite Primary Key A02 Finance M4 The “tables behind the boxes” 1 M ADVISER- M 1 ADVISER advises STUDENT STUDENT M ADVISER-STUDENT ADVISER- ADVISER STUDENT STUDENT employs AID Name DID SID Name Grade AID SID 001 Jones A01 100 Miller HD 001 100 002 Wu A01 200 Schulz P 001 400 1 003 Smith A02 300 Shang AF 002 200 004 Lopez A01 400 Lee D 002 600 DEPARTMENT 005 Greene A02 500 Li D 003 100 600 Ali P 003 300 700 Watson F 005 400 DEPARTMENT 800 Cheng D 005 700 DID Dep. Name Location 005 800 A01 Accounting H69 Foreign Keys A02 Finance M4 ERD (1) 1 coordi M COORDINATOR nates UNIT_OF_STUDY Staff_ID Name Discipline UOS_Code Name Staff_ID Please explain the ERD ERD (2) 1 M UNIT_OF_STUDY has LAB_CLASS UOS_Code Name Semester Lab_ID UOS_Code Room Time Please explain the ERD ERD (3) ENROLMENT 1 M enrols M 1 STUDENT UNIT_OF_STUDY in SID Name Address SID UOS_Code UOS_Code Name Grade Please explain the ERD ERD (4) STREAM 1 M teaches M 1 LECTURER in UNIT_OF_STUDY Staff_ID Name Staff_ID UOS_Code UOS_Code Name Room Time Please explain the ERD A Classic ERD- booking system Design a booking system for hotel room booking Steps in creating an ERD Step 1. Identify entities Business objects you need to store information about Step 2. Identify business rules Find ‘NOUN – verb – NOUN’ relationships in narrative Step 3. Define relationships and represent cardinality Some business objects are actually relationships à associative entity Step 4. Identify attributes Characteristics of entities Represent information that you want to store about entities A little example From complex narrative to business rules: Narrative: “The Faculty of Veterinary Science employs 44 academics, all of which work in one of five research centers. Many academics are also members in at least one research group, which investigate a range of research topics. Research groups do not overlap in their research interests. Each academic can be mentored by another academic.” The task: design a database to store the above information First task: Create an ERD. Example continued Step 1: Identify entities à create list of nouns from text: ― Academics ― Research Center ― Research Group ― Research Topic Are all of these really relevant? ― Faculty Step 2: Business rules to identify relationships: ‘NOUN verb NOUN’ ACADEMIC works in CENTER ACADEMIC is member of RESEARCH GROUP RESEARCH GROUP investigates RESEARCH TOPIC ACADEMIC mentors ACADEMIC Example continued Step 3 - Cardinality 1 mentors M Unary Relationships Unary relationship: relationship between two different instances of an entity. For example, when one employee supervises other employees. In a unary relationship, a link is created between the employee and his/her supervisor. E_ID E_ID Employee Employee Supervisor_ID 1 1 1 M Supervises M Supervises M Supervisee_ID Supervisor_ID What if an employee can have many supervisors (M-M relationship)? What we have learned from this activity Two kinds of important relationships: 1. The transaction (many-to-many) Modelled with an associative entity (e.g., booking, order). Extend the composite primary key (e.g. with time) to allow for multiple transactions between the same subject and object (e.g. hotel guest and room) over time 2. The classifying relationship (one-to-many) Use a TYPE relationship when an attribute isn’t unique to each entity instance, but to a class (type) of entities. In this case you create a new entity (TYPE), and a 1-m relationship with the object entity (e.g. ROOM and ROOM TYPE) You can then assign certain attributes to the TYPE (e.g. Rate as an attribute of ROOM TYPE), and others to the actual object you are classifying (e.g., Floor as an attribute of ROOM). Extend your ERD with further information Further to your analysis so far, the hotel operator wants to incorporate an additional idea into the database. Here is what they have to say: ― “It is important for us to know our room rates. ― Room rates are set according to the type of room (such as single, double, twin, executive suite, etc).” Extend your ERD based on this information. Classifying Relationship 1 M has ROOMTYPE ROOM RT_ID Name Rate Room_NO RT_ID