Week 11 Continued….

Keys: How do we distinguish an entity from other entities of the some entity set?

How do we distinguish a relationship from others in the same relationship set? Use attributes!

Superkeys: is a set of one or more attributes whose values uniquely identify an entity (relationship) in an entity (relationship) set.

Employee: SSN, empName

Candidate key: a super key which no proper subset is a super key. (Minimality)

It is possible to have more than one candidate key.

Primary key: One of the candidate keys chosen identification purposes.

How to choose primary key for strong & weak entities

An entity with a primary key is strong entity. An entity that does not have primary key is weak entity.

Transaction {transaction_number, date, amount}

Unique within an account but between accounts there may be duplication discriminator. (Partial key)

The discrimination of a weak entity set is a set of attributes tat serves to distinguish among entities in the entity set that depend on one particular strong entity.

Primary key of a weak entity

º

Example = {account_number, transaction_number}

Primary Key Of A Relationship Set

Formed by union of primary keys of the associated entity set.

Example 2.3 University database

Ø A professor has a SSN, name, age, rank, research specialty

Ø Projects have a project number, a sponsor name (e.g. NSF), a starting date, an ending date and a budget.

Ø Graduate students have SSN, name, age, and degree program.

ü Each project is managed by one professor. (Known as project principle investigator.)

ü Each project is worked on by one or more professor. (Known as the project co investigators.)

ü Professors can manage and / or works on multiple projects.

ü Each project is worked on by one or more graduate students. (Known as the project’s research assistants.)

ü When graduate students work on a project, a professor must supervise their work on the project, in which case they will have (potentially different) supervisor for each one.

(Exercises 2.2, 2.4, 2.4, 2.5)

Chapter 3: The Relational Model

Well-known Database Management Systems

IBM DB2 family

Informix

Oracle

Sybase

Microsoft Access & SQL Server

Paradox

Example: Relational Model

Students (sid: String, name: String, login: String, age: integer, gpa: real)

An instance:

Domain constraints: sid is a string.

Degree of relation = parity: (fixed) Number of columns or attributes, 5 for this case.

Cardinality of relation: number of records or rows.

A relational database: is asset of relations with distinct relation names.

The relational database schema: is the collection of schemes for the relations in the database.

ISO SQL 86

89

96

CREATE TABLE Students (sid CHAR (20), name CHAR (30), login CHAR (20), age INTEGER, gpa REAL PRIMARY KEY sid)

INSERT INTO Students (sid, name, login, age, gpa) VALUES (500, “Ali”, “ali@cs”, 19, 2.0)

DELETE FROM Students S WHERE S.name= “Ali”

UPDATE FROM Students S SET S.age =S.age+1, S.gpa = S.gpa –1WHERE S.gpa > 3.3

Integrity Constraints Over Relations

Key Constraint

Foreign Constraint

General Constraint

Legal instance of relation satisfies all the integrity constraints.

Key Constraints

It is a statement that a certain minimal subset of a field of a relation is a unique identifier for a tuple. Choose one of the candidate keys as primary key.

CREATE TABLE Students (sid CHAR (20),

Name CHAR (30),

login CHAR (30),

age INTEGER,

gpa REAL,

UNIUE (name, age),

CONSTRAINT StudentsKey PRIMARY KEY (sid))

Constraint key

If constraint is violated, the constraint name is returned to identify error.

Foreign Key Constraints

1-) when you add a tuple to Enrolled make sure that there is matching tuple in Students.

2-) When you delete a student, make sure that all matching tuples of Enrolled are also deleted.

Students (sid, age, ….., partition)

3-) When you update a student, make sure that all matching tuples of Enrolled are also updated.

CREATE TABLE Enrolled (

sid CHAR (20),

cid CHAR (10),

grade CHAR (2),

PRIMARY KEY (sid, cid),

FOREIGN KEY (sid) REFERENCES Students,

FOREIGN KEY (Cid) REFERENCES Courses )

Referential Integrity

Enforcement Steps:

1-) If no matching student exist in its relations reject additions to enrollment.

2-) When a row is deleted from Students,

- Delete all matching enrollment rolls.

- Or disallow deletion from Students.

- Or Modify matching Enrollment tuples so that they refer to do default Students.

3-) What happens if sid of the Students relations is updated a similar item no2

- Update all matching enrollment rolls.

- Or disallow update from Students.

CREATE TABLE Enrolled (

sid CHAR (20),

cid CHAR (10),

grade CHAR (2),

PRIMARY KEY (sid, cid),

FOREIGN KEY (sid) REFERENCES Students,

ON DELETE CASCADE,

ON UPDATE NO ACTION)

The default option is NO ACTION, which means that the action (DELETE OR UPDATE) is to be rejected. Thus ON UPDATE clause in our example could be omitted, with the same effect. The CASCADE keyword says that if a students row is deleted, all Enrolled rows that refer to it are deleted as well. If the UPDATE clause is specified CASCADE, and the sid column of a Students row is updated, this update is also carried out in each Enrolled row that refers to the updated Students row.

In example below if a row in Students deleted, we can switch the Enrollment to a ‘default’ student by using ON DELETE SET DEFAULT.

CREATE TABLE Enrolled (

sid CHAR (20) DEFAULT ‘53666’,

cid CHAR (10),

grade CHAR (2),

PRIMARY KEY (sid, cid),

FOREIGN KEY (sid) REFERENCES Students,

ON DELETE SET DEFAULT // OR IT CAN BE SET TO NULL

)

General Constraints

16 <= age <= 0

0.0 <= gpa <= 4.0

Logical Database Design: How to go from ER model to Relational Model

1-) Entity sets to tables.

CREATE TABLE EMPLOYEE ( . . .

. . .

PRIMARY KEY (SSN))

2-) Relationship sets (without constraints) to tables.

The attributes of the relationship include

- The primary key attributes of each participating entity set as foreign key fields

- Description attributes of the relationship set (if any).

WorksIn2 (ssn, did, address, since)

WorksIn2 (ssn: string, did: integer, address: string, since: date)

CREATE TABLE WorksIn2 (

ssn CHAR (20),

did INTEGER,

adress CHAR (50),

since DATE,

PRIMARY KEY (ssn, did, address),

FOREIGN KEY (ssn) REFERENCES EMPLOYEES,

FOREIGN KEY (did) REFERENCES Departments,

FOREIGN KEY (address) REFERENCES Locations )

CREATE TABLE Reports_To (

superviser_ssn CHAR (11),

subordinate_ssn CHAR (11),

PRIMARY KEY (superviser_ssn, subordinate_ssn)

FOREIGN KEY superviser_ssn) REFERENCES Employees

FOREIGN KEY (subordinate_ssn) REFERENCES Employees

)