INFS5710 Week 04
INFS5710 IT Infra. for BA Database Concept
UNSW Business School
Copyright By PowCoder代写 加微信 powcoder
INFS5710 Week 04
Chapter 5 Advanced Data Modeling 5-1 to 5-3
pp. 168-180
This week we will look at more advanced EER modelling.
Today, some of the designers will totally ignore this section altogether. Why? Because they have in the head already! That is, they know what to do when they implement the physical database.
However, you have to learn because it will help you to think through when designing a database.
INFS5710 Week 1
Copyright © 2012, SAS Institute Inc. All rights reserved.
Database Systems Infrastructure
Entity Relationship Model (ERM)
Normalisation
Prepared by , Feb. 2021
External Data (e.g. Excel)
ETL (Data Cleansing)
Star Schema (De-Normalised)
Data Warehouse
External Data (e.g. Excel)
Data (DW to BD, or vice versa or both)
Unstructured Data (Social Media)
Data Streaming
Structured Data (Internet of Things (IOT))
Data Streaming
Flat Files
Machine Learning
Relational Database
The notes in the speaker notes originally are for myself only, but students saw my notes and they asked for them, so I start to share with you all. It is not perfect but hopefully, it makes sense to you!
For the next two weeks, we will be looking at relational database model, particularly Entity Relationship Modelling or ERM. or sometimes we just call it Entity Relationship Diagram or ERD.
This week we will be looking at relational table, and next week, we will be looking at normalisation of database tables. So, that is the red cylinder.
(“Not Normalised”)
Spark and NoSQL (and other tools)
Hadoop Distributed File System (HDFS) and MapReduce
Reporting (Business Intelligence and Visualisation) and Business Analysis (End Users)
Note: In-Memory Database (e.g. SAP Hana) is an alternative data model not shown here
INFS5710 Week 04
Extended Entity Relationship Model (EERM)
Result of adding more semantic constructs to the original entity relationship (ER) model
EER diagram (EERD): Uses the EER model
©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use.
Extended ER diagram or EERD are looking at more details at the modelling design.
INFS5710 Week 04
PKs and FKs
Please Note: Not all PKs and FKs are shown here, there are more such as Term Id…
Table: Degrees
Table: Class Management
WeChat Account
Table: Students
Remember this from previous lecture, you are a student and enrol in several courses in different classes.
Course Name
Table: Class Enrolment
©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use.
Table: Courses
INFS5710 Week 04
Different Input
What if I were to ask you for your High School results, undergraduate degree, and other degree or diploma, or even your professional membership, I am going to have different results.
For example, for your high school results, in NSW, you are going to have HSC results and ATAR score. How about in China, India, Indonesia, and UK, they all are different, so how do you keep the records?
So how about professional memberships? For
example, an accountant has a CPA, whereas a
plumber need a plumbing certificate (or may be none)
©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use.
In NSW, you are going to have HSC results and ATAR score.
How about in China, India, Indonesia, and UK, they all are different, so how do you keep the records?
INFS5710 Week 04
20/06/2022
Existence Dependence
Existence dependence: Entity exists in the database only when it is associated with another related entity occurrence
Existence independence: Entity exists apart from all of its related entities, and referred to as a strong entity or regular entity
©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a
certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use.
Before looking at subtypes, we have a look at the meaning of existence dependence. As stated in the slide
A good example of existence dependence is you have parents, and you cannot exist without your parents We will come back to this.
We will next go through existence independence first.
INFS5710 Week 04
19/06/2022
Existence Independence (1)
Weak (non- identifying) relationship
• Primary key of the related entity does not contain a primary key component of the parent entity
©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a
certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use.
For Weak (non-identifying) relationship, the Primary key of the related entity does not contain a primary key component of the parent entity.
In this example, you can see 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.
INFS1603-COMM1822
19/06/2022
Existence Independence (2)
Strong (identifying) relationships
• Primary key of the related entity contains a primary key component of the parent entity
©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a
certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use.
For Strong (identifying) relationship, the Primary key of the related entity contains a primary key component of the parent entity
In this example, you can see CRS_Code is a primary key of Course table, and CRS_CODE is a foreign key of Class table. Moreover, CRS_CODE is also part of the primary key of Class table. CRS_CODE is part of the composite primary key for Class table.
INFS5710 Week 04
20/06/2022
Weak Entities
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.
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.
©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a
certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use.
Be careful, weak entity is different from weak relationship!
Weak entity meets two conditions:
a. Existence-dependent: Cannot exist without entity with which it has a relationship
b. Has primary key that is partially or totally derived from the parent entity in the relationship.
In UNSW, employee or PG students can put their children into university childcare. As long as the employee or PG student remains at the university, their children can stay at the childcare. However, as soon as the employee or the PG student departs the university, their children have to leave (FYI – that was the old policy, the new policy allows the children to remain in the childcare, but they will be charged at a higher rate).
So that means a weak entity its existence is depending on the existence on another entity in that system. In this case, the other table is Dependent table.
You express that you model that and you are here purely thinking about it from the perspective of the designer. It is based on the business rules.
INFS5710 Week 04
19/06/2022
Example of A Weak Entity in an ERD
©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use.
As stated in the slide
The Dependent entity in Chen model has a composite primary key consists of EMP_NUM and DEP_NUM columns.
An employee has an emp_num, and it is a PK in Employee entity. As for Dependent entity, an employee can have no child, or one child, or two or more children, thus the cardinality is 0:N (0 (zero) because no child). DEP_NUM is a sequential number to keep track number of children. It is optional as an employee does not need to have a child to work in the company. Hence, it is shown as optional relationship.
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.
As for the cardinality relationship for Dependent entity is (1,1) , i.e., a child must have one parent working in the company. If both parents work in the company, you only have to
connect to one. For example, UNSW childcare, it is connected to the parent who will pay childcare fees
INFS5710 Week 04
Figure 5.2 – Specialization Hierarchy
disjoint – e.g., a pilot cannot be a mechanic
©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use.
Have a look at employee entity, you can see there are three entities join below entity employee.
Employee is a Supertype and it has all the common attributes for all employees, and it has a subtype attribute, or it is called subtype discriminator. In this example, the emp_type has three values, “P”, “M”, and “A”,
In this airline company, there are three entities: one is pilot, one is mechanic, and the last one is accountant. These entities are subtypes.
The employee entity has all the details of employees, but they all work in different jobs. For example, pilot need to have pilot license, but not mechanic and accountant. Employee is the supertype because it has the common attributes of pilot, mechanic, and accountant, which are the subtypes.
Question: what’s happen if an accountant has a pilot license as well? Let’s see later…
INFS5710 Week 04
Entity Supertypes and Subtypes
Entity supertype: Generic entity type related to one or
more entity subtypes
Contains common characteristics
Entity subtype: Contains unique characteristics of each
entity subtype PILOT
The different kinds of instances should each have one or more attributes that are unique to that kind of instance
©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use.
Criteria to determine the usage
There must be different, identifiable kinds of the entity in
the user’s environment
MECHANIC ACCOUNTANT
As we said in the last slide, Employee entity is the Supertype because it contains common attributes or characteristics for pilot, mechanic, and accountant, which are the subtypes.
INFS5710 Week 04
Specialization Hierarchy
Depicts arrangement of higher-level entity supertypes and lower-level entity subtypes
Relationships are described in terms of “is-a”
relationships
An employee “is a” pilot, mechanic, or accountant.
Subtype exists within the context of a supertype
Every subtype has one supertype to which it is directly related
Supertype can have many subtypes
©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use.
Entity supertypes and subtypes are organised into a specialization hierarchy with the high- level entity supertypes, which are the parent entities, and the lower-entity subtypes are the child entities.
The relationships between the entities are described in terms of is-a relationships. So, in our example, an employee is a pilot, mechanic, or accountant.
A subtype must exist within the context of a supertype and must directly associate to one supertype.
A supertype can have many subtypes.
INFS5710 Week 04
Specialization Hierarchy
Provides the means to:
Support attribute inheritance
Define a special supertype attribute known as the
subtype discriminator e.g., EMP_TYPE (pilot, mechanic or accountant)
Define disjoint/overlapping constraints and complete/partial constraints
Can an employee be a pilot and a mechanic?
Must an employee be a pilot, a mechanic, or an accountant?
©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use.
It supports attribute inheritance, which means all attributes are inherited by the entity subtypes.
So, how do you differentiate the different subtypes? The way to do is to have a special subtype attribute called subtype discriminator.
In our example, emp_type attribute is the subtype discriminator.
Figure 5.2 – Specialization Hierarchy
disjoint – e.g., a pilot cannot be a mechanic
©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use.
If you look if you look here between employee entity and dependant entity, the relationship is “has”.
See emp_type attribute in employee entity is the subtype discriminator. So, depending on the value of emp_type attribute, it will branch out differently.
If you look at the category symbol, this is Disjoint, and emp_type is the subtype discriminator. What it means here, for example, is a pilot cannot be a mechanic!
INFS5710 Week 04
Inheritance
Enables an entity subtype to inherit attributes and relationships of the supertype
All entity subtypes inherit their primary key attribute from their supertype
At the implementation level, supertype and its subtype(s) maintain a 1:1 relationship
Entity subtypes inherit all relationships in which supertype entity participates
Lower-level subtypes inherit all attributes and relationships from its upper-level supertypes
©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use.
As stated in the slide
INFS5710 Week 04
Subtype Discriminator
Attribute in the supertype entity that determines to which entity subtype the supertype occurrence is related EMP_TYPE is an attribute of the supertype
Default comparison condition is the equality comparison “is-a” is “=“
©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use.
As stated in the slide
INFS5710 Week 04
Disjoint and Overlapping Constraints
Disjoint subtypes: Contain a unique subset of the
supertype entity set
Known as nonoverlapping subtypes
Implementation is based on the value of the subtype
discriminator attribute in the supertype
Overlapping subtypes: Contain nonunique subsets
©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use.
of the supertype entity set
Implementation requires the use of one discriminator
attribute for each subtype (next slide)
Disjoint subtypes mean the subtypes are independent and not overlapped. For example, P, M and A are not overlapped.
On the other hand, overlapping subtypes are when subtypes entities are overlapped with each other.
See next slide as an example.
INFS5710 Week 04
©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use.
For example of Disjoint versus Overlapping! For example, the books of !
INFS5710 Week 04
overlapping:
a student can also be an employee
Total completeness (Everyone must be an employee, a student or both)
Partial completeness
©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use.
If we look at person entity, a student can be an employee, for example you might be working at a university as well as studying.
For Total Completeness, a person can either be an employee, or a student, or both.
That is why you have two attr
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com