Database Design lecture 2 Database Design The Relational Model Text Ch5 1

Relational Model  In the relational model, data are organized into tables.

 A

table

corresponds to the mathematical concept of a

relation

(come back shortly).

 The table must have a name (a.k.a the relation name).

 Each column must have a name, known as an

attribute

.

 Each row (not including the table heading row) is called a

tuple

tuple. . If the tuple has

n

elements then it is called an

n-

 The number of columns (resp. rows) is called the

degree or arity (resp. cardinality)

of the relation.

Database Design lecture 2 2

Table (Relation) Example 1 A Student table in a university database studentNo Name City Phone 13678765 Barbara 12345678 David Brisbane 44132565 Sydney 76354543 16432527 Darren 16543672 Johnson Melbourne 90113422 Gold Coast 55224433 Database Design lecture 2 3

Relation Schema and Instance  Let

R

be the name of a relation, and

A 1 ,…, A n

be the attributes. Then

R(A 1 ,…, A n )

is called the

schema

of the relation.  Each attribute of a relation must have a fixed set from which to obtain values. Eg, – – Name : the set of all possible names Age: {0, 1, 2,…, 150}  The set corresponding to attribute

A i

is called the

domain

of

A i

, denoted

dom(A i ).

At different times, the relation may contain different tuples. Each possible set of tuples is called a

state

an

instance)

of the relation.

(or Database Design lecture 2 4

Relational Database Schema and Instance  A relational database – a collection of relations.  The schemas of the relations collectively (together with a set of

integrity constraints

– see slide 13) is called the

relational database schema

.  The relations in a database should have distinct names.

 Attributes within the same relation should have distinct names.

 A

database (instance)

consists of the instances (one for each relation) of the relations.

Database Design lecture 2 5

Data Inside a Relation    Atomic value in each cell – The data item in each table cell must be atomic  can not be divided into smaller components  Atomicity is a relative concept, it depends on the application.

A special value NULL can appear in any cell – represents unknown value or non-existent value – eg, in Student table, a student may have no phone number, or his phone number may be unknown. In such cases, the corresponding value is NULL If

t

is a tuple, and

A

is an attribute, then the value of attribute

A

in

t

is denoted

t[A].

Similarly

t[A 1 , A 3

] denotes the subtuple of values of the attributes

A 1

and

A 3

, and so forth.

Database Design lecture 2 6

Mathematical Definition of Relations  Formally, given sets

D

1 ,

D

2 , ….

D n D

1 x

D

2 x … x

D n

Thus a relation is a set of n-tuples ( a

a relation r

1 ,

a

2 , …,

a

is a subset of

n

) where

a i



D i

 Example: if student_number = set of 6 digit numbers student_name = {Jones, Smith, Curry, Lindsay} student_city = {Sydney, Brisbane, Melbourne, GoldCoast} Then

r

= { (100001, Jones, Brisbane), (100002, Curry, GoldCoast), (100003, Lindsay, Brisbane), (100004, Smith, Sydney)} is a relation over student_number x student_name x student_city Database Design lecture 2 7

Properties of Relations    No duplicate tuples within a relation – By definition sets do not contain duplicate elements, hence no tuples should appear more than once.

Ordering of tuples has no significance – – elements in a set has no ordering also in the sense that no information is lost or added when different orders are used. – but it may affect performance!

Ordering of attributes – has no significance in the sense that no information is lost or added if the attributes are ordered differently.

– BUT it is important when a tuple is considered an ordered list - for easy presentation.

– Alternative definition of tuple can make the ordering unimportant – a tuple is a mapping from attributes to values.

Database Design lecture 2 8

Relational Keys  Superkey – An attribute, or a set of attributes, that can uniquely identify a tuple within a relation.

– Every relation has at least one superkey -- all attributes of the relation  Key (Candidate Key) – Superkey (K) such that no proper subset is a superkey within the relation.  In each tuple of R, values of K uniquely identify that tuple.

 Removing any attribute from K, K will no longer be a superkey. Database Design lecture 2 9

Relational Keys  Primary Key (PK) – Candidate key selected to identify tuples uniquely within relation. PK attributes will be underlined.

 Alternate Keys – Candidate keys that are not selected to be primary key.  Foreign Key (FK) – Attribute, or set of attributes, within one relation that matches candidate key of some (possibly same) relation.

Database Design lecture 2 10

Foreign Keys Example Subjects(subjNo, subjName, points) Students(studNo, Name, Addr, Phone) Enrollment(studNo, subjNo, semester, grade) In enrollment, studNo is a FK which references Students subjNo is a FK which references Subjects Database Design lecture 2 11

Another Example of FKs Database Design lecture 2 12

Relational Constraints  Domain constraints – Specify the possible values an attribute can take  Null-value constraints – Specify whether Null value is allowed in an attribute  Key constraints – PK, AK  Entity integrity – No PK value can be NULL  Referential integrity – FK values must either be NULL or appear in corresponding attribute(s) of referenced relation.  Data dependencies, Enterprise rules (later) Database Design lecture 2 13

Example Database Design lecture 2 14

Diagram Representation of FKs Database Design lecture 2 15

Dependent ESSN DEPDENT_NAME SEX BDATE RELATIONSHIP Database Design lecture 2 Alternative Schema Diagram Dept_locations

DNUMBER

DLOCATION Employee FNAME MINT LNAME SSN BDATE ADDRESS SEX SALARY SUPERSSN

DNO

Department DNAME DNUMBER

MGRSSN

MGRSTART DATE Works_on

ESSN PNO

HOURS Project PNAME PNUMBER PLOCATION

DNUM

16