Introduction to Databases for Business Analytics
Week 3 Relational Modelling
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
Relational Database Model
3-1 to 3-7
Relational Database Modelling ❑ Definition relational model ❑ Relational model integrity
❑ From ER diagram to relational model (from entities to schema/tables)
❑ Mapping ER relationships in the relational model
Database Design: Overview
When designing database for an organisation, the processes are:
❑ Gather business requirements.
❑ Develop conceptual model using ER modelling technique (Weeks 1 and 2). ❑ Convert ER model to a set of relations in the relational model (Week 3).
❑ Normalise the relations to remove any anomalies (Weeks 4 and 5).
❑ Implement the database by creating a table for each normalised relations.
Relational Model
❑“A relational model represents data in a two-dimensional table called a relation.”
❑ Relational model includes:
• Relations: two-dimensional tables
• Attributes: the column headers of a relation.
• Tuples: the rows of a relation.
❑ The name of a relation (table) and its set of attributes (column headers) are called a schema for the relation.
❑ The set of schemas for all relations in a design is called a database schema (metadata).
❑ The data dictionary describes the database schema.
❑ Usually implemented in a RDBMS (relational database management system) such as Oracle.
Example of a Relational Model ❑Relational schema for the relation “MOVIE”
• MOVIE (TITLE, YEAR, LENGTH)
❑ Relation
• Every relation has a unique name.
• Every attribute value is atomic.
• Every row is unique.
• Attributes in tables have unique names. • The order of the columns is irrelevant. • The order of the rows is irrelevant.
Example of a Relational Model
Use this format for your assignment!
Relational schema:
PRODUCT (Prod_Code, Prod_Descript, Prod_Price, Prod_On_Hand, Vend_Code)
VENDOR (Vend_Code, Vend_Contact, Vend_AreaCode, Vend_Phone)
ANSI/ISO SQL Data Types
Description
Fixed-length character strings
Variable-length character strings
Integer numbers
Fixed-length bit string (bit array)
Decimal numbers
Floating point numbers
Calendar date
Clock time
Date and time
Relational Database Keys
Example: STUDENT(zID, FName, LName, PassportNum)
e.g., zID, PassportNum, {zID, FName}, {zID, LName}, {zID, PassportNum}, …, {zID, FName, LName, PassportNum} (as long as the attribute(s) can uniquely identifies each row)
e.g., zID or PassportNum e.g., zID
e.g., PassportNum
Integrity Rules
Three basic types of database integrity constraints:
1) Entity integrity: Requiring each row in a table to have a different primary key value.
2) Referential integrity: Requiring the existence of a corresponding primary key in another table for any foreign key value.
3) Domain integrity: Restricting data in a column to its predefined data types.
Integrity Rules
Referential Integrity Example
From Conceptual Model to Relational Model
Conceptual Model to Relational Model ER Model -> Relational Model -> Database
Conceptual Model to Relational Model
In general, each entity will be converted to a relation. The attributes of the entity become the attributes of relation.
❑Eliminate composite and multi-valued attributes.
❑Translate each entity into a relation (table).
❑Translate appropriate relationships into a relation (others might just be a FK link).
Mapping Entities
Mapping Entities
Relational Model
Map all regular entities to relations.
Mapping Composite Attributes
Composite Attribute
Mapping Multi-Valued Attributes
EmployeeID
EmployeeID
EMPLOYEE_SKILL
EmployeeID
SkillDescription
Multi-valued attribute becomes a second relation.
Mapping 1:1 Relationships
Weak entity
ER relationship is expressed through FK reference.
Mapping Weak Entities
ER relationship is expressed through FK reference (FK also a PK).
Weak entity
Mapping 1:M Relationships
Weak entity
ER relationship is expressed through FK reference.
Mapping an M:N Relationship
ER relationship forms a relation in itself.
Weak (composite) entity
Mapping 1:1 Recursive Relationships
ER relationship forms a relation in itself.
MARRIAGE (Emp_ID1, Emp_ID2, Date) EMPLOYEE (Emp_ID, Emp_Name, DofB, …)
Mapping 1:M Recursive Relationships
ER relationship forms
a relation in itself. values of the same relation.
❑ In the example, for some employees the Manager_ID will be “NULL” because they do not have a manager.
EMPLOYEE (Emp_ID, EmpName, DeptID, DOB, Manager_ID)
❑ A recursive FK is a foreign key in a relation that references the primary key
Mapping M:N Recursive Relationships
Course_Name
Course_Name
Prerequisite
COURSE (Course_ID, Course_Name, …) PREREQUISITE (Course_ID, PrerequisiteCourse_ID, …)
ER relationship forms a relation in itself.
Prerequisite
Mapping Super/Subtype Relations (1)
EMPLOYEE (EmpID, Name, DofB, FlightNo, FlightHrs, SkillType, Salary, HourlyRate, ContractNo) Create only one relation (for the supertype). Or…
Mapping Super/Subtype Relations (2)
PILOT (P_EmpID, Name, DofB, FlightNo, FlightHrs)
MECHANIC (M_EmpID, Name, DofB, SkillType, Salary) ACCOUNTANT (A_Emp_ID, Name, DofB, HourlyRate, ContractNo)
Mapping Super/Subtype Relations (3)
EMPLOYEE (EmpID, Name, DofB, EmpType) PILOT (P_EmpID, FlightNo, FlightHrs)
MECHANIC (M_EmpID, SkillType, Salary) ACCOUNTANT (A_EmpID, HourlyRate, ContractNo)
Create separate relations for each subtype and the supertype.
Header – Details Relationship
Referential Integrity in Cascading Integrity
Summary of Most Important Rules
❑ ER entity types become relational schemata (relations).
❑ ER relationships become relational schemata OR references with FKs in the schemata/tables.
❑ ER attributes of an entity type become attribute column headers in the schemata.
❑ Entity instances are the rows (tuples, relational instances) in the actual tables.
❑ Connectivity and cardinality are indirectly expressed through existence of schema, references with FKs, number of rows going into any particular relation, settings such as NOT NULL for the FK columns in the data dictionary, etc.
Recap: W3 Learnings
❑ Relational Database Modelling • Definition relational model.
• Relational model integrity.
• From ER diagram to relational model (from entity types and entity instances to schemata and tables).
• Mapping specific ER relationships to a relational model.
Please email your question(s) to
Source: ealt.ca
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com