代写代考 INFO20003 Database Systems

INFO20003 Database Systems
Dr Renata Borovica-Gajic
Lecture 04 Relational Model & Translating ER diagrams
INFO20003 Database Systems Week 2

• Relational Model
• Keys & Integrity Constraints
• Translating ER to Logical and Physical Model
Readings: Chapter 3, Ramakrishnan & Gehrke, Database Systems
INFO20003 Database Systems © University of Melbourne 3

Relational Data Model
• Data Model allows us to translate real world things into structures that a computer can store
• Many models: Relational, ER, O-O, Network, Hierarchical, etc.
• Relational Model:
–Rows & Columns (Tuples/records and Attributes/fields) –Keys & Foreign Keys to link Relations
53666 Carnatic101 5
53666 Reggae203 5.5
53650 Topology112 6 53666 History105 5
53666 Jones
53688 Smith
53650 Smith 19 4.8
18 5.4 18 4.2
INFO20003 Database Systems © University of Melbourne 4

Relational Database: Definitions
• Relational database: a set of relations. • Relation: made up of 2 parts:
–Schema : specifies name of relation, plus name and type of each column (attribute).
Example: Students(sid: string, name: string, login: string, age: integer, gpa: real)
–Instance : a table, with rows and columns. #rows = cardinality
#fields = degree (or arity)
• You can think of a relation as a set of rows or tuples. – all rows are distinct, no order among rows
INFO20003 Database Systems © University of Melbourne 5

Example Instance of Students Relation

Cardinality = 3, degree (arity) = 5, all rows distinct
INFO20003 Database Systems © University of Melbourne 6

Logical Design: ER to Relational Model
In logical design entity set becomes a relation. Attributes become attributes of the relation.
Conceptual Design: Logical Design:
SSN Name Age
Employee (ssn, name,
INFO20003 Database Systems © University of Melbourne 7

ER to Logical to Physical
In physical design we choose data types
1. Conceptual Design: SSN Name Age
2. Logical Design: Employee (ssn, name, age)
3. Physical Design:
(ssn CHAR(11),
name VARCHAR(20), age INTEGER)
INFO20003 Database Systems © University of Melbourne 8

The Entire Cycle
1. Conceptual Design 2. Logical Design
3. Physical Design
4. Implementation
5. Create Instance
INFO20003 Database Systems © University of Melbourne 9

The Entire Cycle
1. Conceptual Design:
SSN Name Age
4. Implementation: CREATE TABLE Employee
(ssn CHAR(11), name VARCHAR(20), age INTEGER, PRIMARY KEY (ssn))
5. Instance: EMPLOYEE
2. Logical Design: Employee (ssn,
name, age)
3. Physical Design:
(ssn CHAR(11),
name VARCHAR(20), age INTEGER)
0983763423
9384392483
3743923483
INFO20003 Database Systems © University of Melbourne 10

Creating Relations in SQL
Example: Creating the Students relation.
CREATE TABLE Students (sid CHAR(20),
name CHAR(20), login CHAR(10), age INTEGER, gpa FLOAT)
The type (domain) of each field is specified, and enforced by the DBMS whenever tuples are added or modified.
INFO20003 Database Systems © University of Melbourne 14

Relational Model
• Relational Model & SQL overview
• Keys & Integrity Constraints
• Translating ER to Logical and Physical Model
Readings: Chapter 3, Ramakrishnan & Gehrke, Database Systems
INFO20003 Database Systems © University of Melbourne 17

• Keys are a way to associate tuples in different relations
• Keys are one form of integrity constraint (IC)
• Example: Only students can be enrolled in subjects.
53666 15-101 C 53666 18-203 B
53650 15-112 A 53666 15-105 B
FOREIGN Key
53666 Jones
53688 Smith
53650 Smith 19 3.8
PRIMARY Key
18 3.4 18 3.2
INFO20003 Database Systems © University of Melbourne 18

Primary Keys
• A set of fields is a superkey if no two distinct tuples can have same values in all key fields
• A set of fields is a key for a relation if it is a superkey and no subset of the fields is a superkey (minimal subset)
• Out of all keys one is chosen to be the primary key of the relation. Other keys are called candidate keys
• Each relation has a primary key
• Your turn:
1. Is sid a key for Students?
2. What about name?
3. Is the set {sid, gpa} a superkey? Is the set {sid, gpa} a key?
4. Find a primary key from this set {sid, login}
INFO20003 Database Systems © University of Melbourne 19

Primary and Candidate Keys in SQL
• There are possibly many candidate keys (specified using UNIQUE), one of which is chosen as the primary key. Keys must be chosen carefully.
For a given student and course, there is a single grade.
CREATE TABLE Enrolled (sid CHAR(20)
CREATE TABLE Enrolled (sid CHAR(20)
cid CHAR(20),
grade CHAR(2), PRIMARY KEY (sid), UNIQUE (cid, grade))
“Students can take only one course, and no two students in a course receive the same grade.”
cid CHAR(20),
grade CHAR(2), PRIMARY KEY (sid,cid))
INFO20003 Database Systems © University of Melbourne 20

Foreign Keys & Referential Integrity
• Foreign key : A set of fields in one relation that is used to ‘refer’ to a tuple in another relation. Foreign key must correspond to the primary key of the other relation.
• If all foreign key constraints are enforced in a DBMS, we say referential integrity is achieved.
INFO20003 Database Systems © University of Melbourne 21

Foreign Keys in SQL
Example: Only students listed in the Students relation should be allowed to enroll in courses.
• sid is a foreign key referring to Students
CREATE TABLE Enrolled (sid CHAR(20),
cid CHAR(20),
grade CHAR(2),
PRIMARY KEY (sid,cid),
FOREIGN KEY (sid) REFERENCES Students )
53666 15-101 C 53666 18-203 B
53650 15-112 A 53666 15-105 B
53666 Jones
53688 Smith
53650 Smith 19 3.8
18 3.4 18 3.2
INFO20003 Database Systems © University of Melbourne 22

Enforcing Referential Integrity
• Consider Students and Enrolled; sid in Enrolled is a foreign key that references Students.
• What should be done if an Enrolled tuple with a non-existent student id is inserted? (Reject it!)
• What should be done if a Students tuple is deleted?
–Also delete all Enrolled tuples that refer to it?
–Disallow deletion of a Students tuple that is referred to?
–Set sid in Enrolled tuples that refer to it to a default sid?
–(In SQL, also: Set sid in Enrolled tuples that refer to it to a special value null, denoting `unknown’ or `inapplicable’.)
• Note: Similar issues arise if primary key of Students tuple is updated.
INFO20003 Database Systems © University of Melbourne 23

Integrity Constraints (ICs)
• IC: condition that must be true for any instance of the database; e.g., domain constraints.
–ICs are specified when schema is defined. –ICs are checked when relations are modified.
• A legal instance of a relation is one that satisfies all specified ICs.
–DBMS should not allow illegal instances.
INFO20003 Database Systems © University of Melbourne 24

Logical Design: Recap
In logical design entity set becomes a relation. Attributes become attributes of the relation.
Conceptual Design: Logical Design:
SSN Name Age
Employee (ssn, name,
INFO20003 Database Systems © University of Melbourne 26

Multi-valued attributes in logical design
• Multi-valued attributes need to be unpacked (flattened) when converting to logical design. *There is an alternative of creating a lookup table discussed in the next
For employees we need to capture their home phone number and work
phone number. Conceptual Design:
SSN Name Age
Multi-valued attribute
Logical Design: Employee (ssn,
age, home_num, work_num)
INFO20003 Database Systems 27

Composite attributes in logical design
• Composite attributes need to be unpacked (flattened) when converting to logical design.
For employees we need to capture an address consisting of a postcode,
street name and number.
Conceptual Design:
Logical Design: Employee (ssn,
Street name Street num
Composite attribute
age, postcode, street_name, street_num)
INFO20003 Database Systems 28

ER to Logical Design: Many to Many
Conceptual Design:
Name since SSN Age
Logical Design:
In translating a many-to-many relationship set to a relation, attributes of a new relation must include:
1. Keys for each participating entity set (as foreign keys). This
set of attributes forms a superkey of the relation. 2. All descriptive attributes.
Department
INFO20003 Database Systems © University of Melbourne 29

ER to Logical Design: Many to Many
Conceptual Design:
Logical Design:
Employee (ssn, name
Works_In (ssn, did,
since Name ID
Department (did, dname,
Department
Keys from connecting entities become PFK
This is called an associative entity
Note: Underline = PK, italic and underline = FK, underline and bold = PFK
INFO20003 Database Systems © University of Melbourne 30

Logical to Physical Design
Logical Design:
Employee (ssn, name, age) Department (did, dname, budget) Works_In (ssn, did, since)
Note: Underline = PK, italic and underline = FK, underline and bold = PFK
Physical Design:
(ssn CHAR(11), name VARCHAR(20), age INTEGER)
Department (did INTEGER,
dname VARCHAR(20), budget FLOAT)
ssn CHAR(11), did INTEGER, since DATE)
INFO20003 Database Systems © University of Melbourne 31

Implementation (Create table)
Logical Design:
Employee (ssn, name, age) Department (did, dname, budget)
Works_In (ssn, did, since) Implementation:
CREATE TABLE Employee (ssn CHAR(11),
name VARCHAR(20), age INTEGER, PRIMARY KEY (ssn))
CREATE TABLE Works_In (ssn CHAR(11),
did INTEGER,
since DATE,
PRIMARY KEY (ssn, did),
FOREIGN KEY (ssn) REFERENCES Employee, FOREIGN KEY (did) REFERENCES Department)
Note: Underline = PK, italic and underline = FK, underline and bold = PFK
CREATE TABLE Department (did INTEGER,
dname VARCHAR(20), budget FLOAT, PRIMARY KEY (did))
INFO20003 Database Systems © University of Melbourne 32

Example Instances
Department
Purchasing
0983763423
9384392483
3743923483
0983763423
1 Jan 2003
0983763423
2 Jan 2003
9384392483
1 Jun 2002
INFO20003 Database Systems © University of Melbourne 33

ER to Logical Design: Ternary relationship
In translating a many-to-many relationship set to a relation, attributes of the relation must include:
Keys for each participating entity set (as foreign keys). This set of attributes forms a superkey for the relation.
All descriptive attributes.
Logical Design:
Contracts (
supplier_id, part_id, department_id)
Note: Underline = PK, italic and underline = FK, underline and bold = PFK
Departments
INFO20003 Database Systems © University of Melbourne 34

ER to Logical to Implementation: Ternary relationship
Logical Design:
Contracts (
supplier_id, part_id, department_id)
Implementation:
CREATE TABLE Contracts (
supplier_id INTEGER,
part_id INTEGER,
department_id INTEGER,
PRIMARY KEY (supplier_id, part_id, department_id), FOREIGN KEY (supplier_id) REFERENCES Suppliers, FOREIGN KEY (part_id) REFERENCES Parts,
FOREIGN KEY (department_id) REFERENCES Departments)
Departments
INFO20003 Database Systems © University of Melbourne 35

Review: Key Constraints in ER
• Each department has at most one manager, according to the key constraint on Manages.
Age ID manages
Department
INFO20003 Database Systems © University of Melbourne 36

Logical design: Key Constraints
Age ID manages
Logical Design:
Employee (ssn, name, age) VS.
Department (did, dname, budget) Manages (ssn, did, since)
Employee (ssn, name, age) Department (did, dname, budget,
ssn, since)
Department
Note: Underline = PK, italic and underline = FK, underline and bold = PFK
INFO20003 Database Systems © University of Melbourne 37

Key Constraints in SQL
Implementation:
CREATE TABLE Employee (ssn CHAR(11),
name VARCHAR(20), age INTEGER, PRIMARY KEY (ssn))
CREATE TABLE Department (did INTEGER,
dname CHAR(20), budget FLOAT, ssn CHAR(11),
since DATE,
PRIMARY KEY (did) FOREIGN KEY (ssn)
Age ID manages
CREATE TABLE Manages (ssn CHAR(11),
did INTEGER,
since DATE,
PRIMARY KEY (ssn, did),
FOREIGN KEY (ssn) REFERENCES Employees,
FOREIGN KEY (did) REFERENCES Departments)
Department
REFERENCES Employees) Which one is better?
INFO20003 Database Systems © University of Melbourne 38

Logical Design: Key Constraints Rule
• RULE: Primary key from the many side becomes a foreign key on the one side
• This is the way to ensure that the key constraint holds
CREATE TABLE Department (did INTEGER,
dname CHAR(20), budget FLOAT, ssn CHAR(11),
since DATE,
PRIMARY KEY (did) FOREIGN KEY (ssn)
REFERENCES Employee)
Each department will have a single manager
INFO20003 Database Systems © University of Melbourne 39

Review: Participation Constraints
• Does every department have a manager?
–If so, this is a participation constraint: the participation of
Departments in Manages is said to be total.
Department
INFO20003 Database Systems © University of Melbourne 40

Participation Constraints in SQL
• We specify total participation with key words NOT NULL
–NOT NULL = this field cannot be empty
CREATE TABLE Department ( did INTEGER NOT NULL, dname CHAR(20) NOT NULL, budget FLOAT NULL,
ssn CHAR(11) NOT NULL,
since DATE NULL,
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employee
ON DELETE NO ACTION)
NOTE: Every time we create a table or draw a physical design we should specify whether attributes are NULL or NOT NULL. We haven’t done it in each slide of this lecture due to clarity and lack of space – but don’t forget this in your design/implementation!
INFO20003 Database Systems © University of Melbourne 41

Review: Weak Entities
• A weak entity can be identified uniquely only by considering the primary key of another (owner) entity.
INFO20003 Database Systems © University of Melbourne 42

Translating Weak Entities
• Weak entity set and identifying relationship set are translated into a single table.
–When the owner entity is deleted, all owned weak entities must also be deleted.
Logical Design:
Dependent (dname, age, cost, ssn)
Implementation:
Note: Underline = PK, italic and underline = FK, underline and bold = PFK
CREATE TABLE Dependent (
dname CHAR(20) NOT NULL,
age INTEGER NULL,
cost DECIMAL(7,2) NOT NULL,
ssn CHAR(11) NOT NULL,
PRIMARY KEY (dname, ssn),
FOREIGN KEY (ssn) REFERENCES Employees
ON DELETE CASCADE)
INFO20003 Database Systems © University of Melbourne 43

Relational Model: Summary
• A tabular representation of data.
• Simple and intuitive, currently the most widely used.
• Integrity constraints can be specified based on application semantics. DBMS checks for violations.
– Two important ICs: primary and foreign keys
– In addition, we always have domain constraints.
• Rules to translate ER to logical design (relational model)
INFO20003 Database Systems © University of Melbourne 44

What’s examinable
• Translate conceptual (ER) into logical & physical design • Understand integrity constraints
• Use DDL of SQL to create tables with constraints
INFO20003 Database Systems © University of Melbourne 45

Next Lecture
• ER Modelling Example with MySQL Workbench
‒ You will need this for workshops/labs (and assessment)
INFO20003 Database Systems © University of Melbourne 46

程序代写 CS代考加微信: powcoder QQ: 1823890830 Email: powcoder@163.com

Related Posts