• A subset of the cartesian product A₁ x ... x A_n
• In practice it is represented as a two dimensional table. 
• Columns are called attributes and rows are called tuples
• Each row represents an n-tuple of R
• Ordering of rows is immaterial
• All rows are distinct
• Ordering of attributes is significannt

Student(id:integer, lastName:string)

Specification of each attribute and the order in which they appear.

It is static

A collection of these gives us the relational database schema

Transform into normal form

[image]

A set of attributes FK in a relation R is a foreign key of R that references the relation S if it satisfies:

• The attributes in FK have the same domain(s) as the primary key of S
• In the current state of R, the value of the FK exists as the value of the primary key in S or is NULL

drop table if exists Student;

create table Student

(

ID integer PRIMARY KEY,

email varchar(20) UNIQUE NOT NULL,

lastName varchar (20),

firstName varchar(20),

DOB date

);

drop table if exists Course;

create table Course;

(

Code char(8) PRIMARY KEY,

title VARCHAR(50

);

drop table if exists Transcript;

create table Transcript

(

Student_Id Integer,

Course_Code char(8),

mark integer default 0,

PRIMARY KEY(Student_ID, Course_Code),

FOREIGN KEY (Student_ID) REFERENCES Student(Id),

FOREIGN KEY(Course_Code) REFERENCES Course(Code)

);

• Conceptual - Ideas; This is for client and stakeholders and is independent of the database that will eventually emerge.
• Logical - High Level Design; We identify entities or objects in the system, the attributes of properties of the entities we need to store and the relationship between objects.
• Physical - Low Level; We have chosen a DBMS and now map the items into the physical tables. Performance optimisation may be needed here.

The resulting diagram is called an entity relation (ER) diagram

Strong entities are in sense independant and can exists indepentendtly.

Weak entities can only exist when there is a relationship with a strong entity.

Pet would be a weak entity which is defined an identifying relationship with its owner

Select σ operation acts on a single relation and acts as a filter keeping only th tuples that satisfy a qualifying relation. (WHERE)

e.g. σ_mark>70

σCourse_Code=Comp1008 Λ mark > 50

Show variations for

σ_{Course_code = COMP1008 Λ mark > 50}(Transcript)

σ_{Course_code = COMP1008}(σ_{COMP1008 Λ mark > 50}(Transcript))

σ_{COMP1008 Λ mark > 50}(σ_{Course_code = COMP1008}(Transcript)

∏ projection oeprator constructs a new relation form selected columsn of R (SELECT)

∏_{LastName, FirstName}(Student)

The theta join, joins a table in the value specified

Student |x|_{id=Student_id} Transcript

• Relations R and S have schema with identical sets of attributes
• The order of attributes in each relation is identical