Introduction to Databases for Business Analytics
Week 2 Entity Relationship (ER) Modelling Part 2
Term 2 2022
Lecturer-in-Charge: Kam-Fung (Henry) : Tutors:
Copyright By PowCoder代写 加微信 powcoder
PASS Leader:
(Tutor-in-Charge) Liam Li
• There are some file-sharing websites that specialise in buying and selling academic work to and from university students.
• If you upload your original work to these websites, and if another student downloads
and presents it as their own either wholly or partially, you might be found guilty of collusion — even years after graduation.
• These file-sharing websites may also accept purchase of course materials, such as copies of lecture slides and tutorial handouts. By law, the copyright on course materials, developed by UNSW staff in the course of their employment, belongs to UNSW. It constitutes copyright infringement, if not academic misconduct, to trade these materials.
Acknowledgement of Country
UNSW Business School acknowledges the Bidjigal (Kensington campus) and Gadigal (City campus) the traditional custodians of the lands where each campus is located.
We acknowledge all Aboriginal and Islander Elders, past and present and their communities who have shared and practiced their teachings over thousands of years including business practices.
We recognise Aboriginal and Islander people’s ongoing leadership and contributions, including to business, education and industry.
UNSW Business School. (2022, May 7). Acknowledgement of Country [online video]. Retrieved from https://vimeo.com/369229957/d995d8087f
Entity Relationship Modelling (from week 1)
Weak Entity
Advanced Entity Relationship Modelling
Supertype Subtype
Convert from an Entity Relationship Model to a Relational Model
Recap: ER Modelling 1
❑ Data Modelling:
Data model as a (relatively) simple abstraction of the complex real-world.
One modelling technique to design a database: Entity Relationship Modelling.
❑ Entity Relationship Modelling components: Entity Types and Entity Instances
Attributes and Values
Relationships
Connectivity and Cardinality
Recap: ER Modelling 1
Foreign key (FK)
Multivalued attribute
Derived attribute
A1 E1 1 r1 M E2
Primary key (PK)
Binary relationship
1 (1,1) (1,1) 1
Unary relationship
• Entity and PK
• Attribute
• Relationship
• Connectivity
• Cardinality
Ternary relationship
Connectivity and Cardinality
(0, 3) (1, 1)
Cardinality
One-to-Many Relationship
Cardinality
Many-to-Many Relationship
How to read this?
• A professor teaches (0, 3) classes. A class is taught by (1, 1) professors.
• A student enrolls in (1, 6) classes. A class has enrolled in it (0, 30) students.
Existence Dependence and Independence
Existence Dependence and Independence
Existence dependence: Entity exists in the database only when it is associated with another related entity occurrence
e.g., parents & children
Existence independence: Entity exists apart from all of its related entities, and referred to as a strong entity or regular entity
e.g., customer & product in a supermarket
Weak (Non-identifying) Relationship
Primary key of the related entity does not contain a primary key component of the parent entity.
CRS_CODE is a primary key of Course table, and CRS_CODE is a foreign key of Class table.
CRS_CODE is a foreign key, but it is not part of the primary key of Class table.
Strong (Identifying) Relationship
Primary key of the related entity
contains a primary key component of the parent entity.
CRS_CODE is a primary key of Course table, and CRS_CODE is a foreign key of Class table.
CRS_CODE is also part of the primary key of Class table. CRS_CODE is part of the composite primary key for Class table.
Weak Entity
Weak Entity
Weak entity is an entity that relies on the existence of another (strong or independent) entity. It has a primary key (PK) that is partially or totally derived from the parent entity in the relationship.
e.g., parents & children
Weak entity meets two conditions:
Existence-dependent: Cannot exist without entity with which it has a relationship.
Has primary key that is partially or totally derived from the parent entity in the relationship.
Database designer usually determines whether an entity can be described as weak based on the business rules.
Example of a Weak Entity in an ERD
Symbol to indicate
Weak Entity
Weak Entity: DEPENDENT
As for fulling the conditions:
a. Existence-dependent: Cannot exist without entity with which it
has a relationship – in this case it is the DEPENDENT entity. A child must exist with one of his/her parents.
b. Has primary key that is
partially or totally derived from the parent entity in the relationship – in this case, EMP_NUM in DEPENDENT entity is associated with EMP_NUM of the EMPLOYEE table.
Why (1, 1)?
• A child must have one parent working in the company.
• If both parents work in the company, you only have to connect to one. E.g., UNSW childcare: it is connected to the parent who will pay childcare fees 🙂
Example of a Weak Entity (with Attributes)
Composite PK
Weak Entity
Existent-Dependent Relationship between EMPLOYEE & DEPENDENT
Strong Entity
FName LName
Other attributes
Example of a Weak Entity
Desirable Primary Key Characteristics
Unique value Cannot be null
Non intelligent Should not have embedded semantic meaning, e.g., use zID as PK
rather than name
No change over time Name, marital status may change
Remember a PK can be someone’s foreign key, multiple attributes make it hard to link tables
Preferably single-attribute
Preferably numeric To avoid typing errors; Can use auto-increment, e.g., zID
Security-compliant
Using Social Security Number (SSN) as a SID is a bad idea.
Candidate Key and Primary Key
Candidate Keys:
• PassportNum
• DriverLicenceNum
PassportNum
DriverLicenceNum
Plan: ER Modelling 2
❑Enhanced Entity Relationship Modelling ▪Composite entity (bridge entity) ▪Supertype and subtype
▪Generalisation and specialisation
▪ Constraints (completeness, disjointness) ❑ Exercise
Database Design Process Modelling
Develop Convert to
Apply normalisation
Conceptual Model
Relational Model
Logical Model
Implement DBS
Composite Entity
Composite Entity
A composite entity (bridge entity, associative entity) is an entity type that associates the instances of one or more entity types. It contains attributes that are peculiar (singular) to the relationship between those entity instances.
❑ The composite entity builds a bridge between the original entities.
❑ The composite entity is composed of the PKs of the original entities. ❑ The composite entity may contain additional attributes.
e.g., enrolment grades
STUDENT M STUDENT 1 M
N CLASS N 1 CLASS
A regular M:N Relationship
A composite entity dealing with M:N Relationship
Composite Entity
❑ M:N relationships (many-to-many relationships) should be avoided.
❑ Relational databases can only handle 1:M relationships (one-to-many relationships).
❑ M:N relationships should be decomposed (broken down) to 1:M relationships by creating a composite entity.
❑ The composite entity builds a bridge between the original entities.
❑ The composite entity is composed of the PKs of the original entities.
❑ The composite entity is existence-dependent on the original entities.
❑ The composite entity may contain additional attributes.
Composite Entity
Relational databases can only handle 1:N relationships (one-to-many
relationships) or 1:1 relationships.
M:N relationships (many-to-many relationships) should be avoided (via
building composite entity).
A M:N relationship should be decomposed (broken down) to two 1:M
relationships by creating a composite entity.
STUDENT M ENROLL STUDENT 1 M
N CLASS N 1 CLASS
A regular M:N Relationship
A composite entity dealing with M:N Relationship
Without Composite Entity (Original)
CLASS_CODE
CLASS_TIME
CLASS_SECTION
Connectivity
Cardinality
Enroll Grade
With Composite Entity
CLASS_CODE
CLASS_TIME
M1 M1 (1, N) (1, 1) (1, 1) (0, N)
CLASS_SECTION
STU_NUM CLASS_CODE
Enroll Grade
Inappropriate Approach
STU_NUM STU_LNAME
CLASS_CODE
1602, 1603 1602, 2603 5992
5992, 5993
CLASS_CODE CRS_CODE PROF_NUM
1602 CIS-200 700
1603 CIS-300 510
2603 SAD-100 240
5992 GM-200 350
5993 ADB-300 120
STU_NUM STU_LNAME
Correct Approach
001 1602 001 1603 002 1602 002 2603 003 5992 004 5992 004 5993
CLASS_CODE
CLASS_CODE ClassName PROF_NUM
1602 CIS-200 700
1603 CIS-300 510
2603 SAD-100 240
5992 GM-200 350
5993 ADB-300 100
Ternary Relationship Without Composite Entity
Ternary Relationship With Composite Entity
✓ A guest can be included in many schedule records;
✓ A schedule record includes only one guest;
✓ A room can be included in many schedule records;
✓ A schedule record includes only one room;
✓ An event can have many schedule records;
✓ A schedule record is only for one event.
Advanced Data Modeling
5-1 to 5-3
Supertype and Subtype
Supertype and Subtype
“A supertype is a more generic entity type compared to its subtypes.”
“A subtype is a more specific entity type compared to its supertype.”
A subtype entity inherits all attributes of the supertype.
A subtype has additional, specific attributes.
An instance (occurrence) of a subtype is also an instance (occurrence) of the supertype.
(The other way around, an instance of the supertype may or may not be an instance of one or more subtypes.)
Supertype and Subtype
Relationships in which all instances participate
Attributes shared by all entities
General entity type
and so forth
Specialised versions of supertype
Relationships in which only specialised versions participate
Attributes unique to subtype 1
Attributes unique to subtype 2
Example of Supertype and Subtype
Explain later
Generalisation and Specialisation
Generalisation and Specialisation
Generalisation: The process of defining a general entity type from a set of specialised entity types. It is a bottom-up process from subtypes to supertypes.
Specialisation: The process of defining one or more subtypes of the supertype. It is a top-down process from supertypes to subtypes.
Generalisation
Question: How to generalise the listed two entity types: CAR and TRUCK?
Generalisation
What are the common attributes?
• Vehicle_ID
• Vehicle_Name (Make, Model)
• Engine Displacement
What are the specific attributes for Car?
• No_of_Passengers
Whatarethespecific attributes for Truck?
• Capacity
• Cab_Type
Specialisation
How to specialise the entity type PART??
Specialisation
Specialisation
How to specialise the entity type Staff?
Specialisation
Constraints
Constraints
❑ The Completeness Constraint describes whether an instance of a
supertype must also be an instance of at least one subtype.
❑ The Disjointness Constraint describes whether an instance of a supertype may simultaneously be a member of two (or more) subtypes.
Constraints
A staff must be either an academic staff or admin staff (total specialisation rule): double line.
An academic staff may also be an admin staff (overlap rule): o.
Constraints
Common sense tells us:
A vehicle may be a car or truck or something else (partial specialisation): single line.
A car must not (CANNOT) a be truck (disjoint rule): d.
Constraints
▪A part must be purchased or manufactured (total specialisation rule): double line.
▪A manufactured part may also be a purchased part (overlap rule): o.
Completeness Constraint
❑ Specifies whether each supertype occurrence must also be a member of at least one subtype
• Partial completeness: Not every supertype occurrence is a member of a
VEHICLE – Car, Truck or something else such as Bus (which is not listed out)
• Total completeness: Every supertype occurrence must be a member of any
STAFF – Academic Staff & Admin Staff
Disjoint and Overlapping Constraints
❑ Disjoint subtypes: Contain a unique subset of the supertype entity
• Known as nonoverlapping subtypes
• Implementation is based on the value of the subtype discriminator attribute in the supertype
❑ Overlapping subtypes: Contain nonunique subsets of the
VEHICLE – Car vs Truck
supertype entity set
STAFF – Academic Staff & Admin Staff
• Implementation requires the use of one discriminator attribute for each subtype
Subtype Discriminator(s)
Subtype discriminator(s) are the attribute(s) of the supertype that determine (code, note, identify) the target subtype.
Disjoint Constraint Rule: One attribute.
Overlapping Constraint Rule: Composite attribute/several attributes.
A simple attribute (Vehicle_Type) with different possible values indicates the subtype (disjoint rule).
Vehicle_Type
The VEHICLE is a CAR.
The VEHICLE is a TRUCK.
The VEHICLE is neither a CAR nor a TRUCK.
Acomposite attribute (Staff_Type) with sub- attributes (with “Yes”/“No” values) indicates the subtype (overlap rule).
Staff_Type
Academic Staff
Admin Staff
The Staff is a member of the Academic Staff subtype.
The Staff is a member of the Admin Staff subtype.
The Staff is both an Academic Staff and an Admin Staff.
How about No / No?
Specialisation Hierarchy Constraint Scenarios
A simple attribute (Employee_Type) with different possible values indicates the subtype (disjoint rule).
Can the Empolyee_Type be null?
ER Modelling Guideline
❑ The data items should be put into an entity (logical group).
❑ For each entity, there should be a Primary key that uniquely identifies individual members of entity type.
❑ There should be no redundant data in the model.
❑Ask yourself the following questions:
• What are the relevant entities here?
• What are the relevant relationships here?
• Can I generalise some entities?
❑Document your assumptions as you go.
❑Leave connectivity and cardinalities until the end.
Source: canningtonvet.com.au
Public Holiday Arrangement in Week 3 ❑No lecture on Queen’s Birthday 13 June (Monday)!
❑ A lecture recording of Week 3 will be uploaded on 14 June by 12 pm.
❑Tutorials will go as usual!!
Draw an ER diagram for this situation (state any assumptions that you make). Based on the ER diagram, draw the relevant relational model.
• The firm has a number of sales offices in several states. Attributes of sales office include
Office_number (identifier) and Location.
• Each sales office is assigned one or more employees. Attributes of employee include Employee_ID
(identifier) and Employee_Name. An employee must be assigned one only one sales office.
• For each sales office, there is always one employee assigned to manage that office. An employee
may manage only the sales office to which he or she is assigned.
• The firm lists property for sale. Attributes of property include Property_ID (identifier) and Location.
Components of Location include Address, City, State, and Zip_Code.
• Each unit of property must be listed with one (and only one) of the sales offices. A sales office may
have any number of properties listed or may have no properties listed.
• Each unit property has one or more owners. Attributes of owners are Owner_ID (identifier) and
Owner_Name. An owner may own one or more units of property. An attribute of the relationship between property and owner is Percent_Owned.
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com