www.cardiff.ac.uk/medic/irg-clinicalepidemiology
Relational data model
Copyright By PowCoder代写 加微信 powcoder
Information modelling
& database systems
in this lecture, we will learn what the relational data model is all about
data model and constraints
How is data organised according to this data model?
keys and foreign keys
Data model
data model describes
structure of the data
operations on the data
constraints on the data
available data models
relational, object-relational
semi-structured, object-oriented
hierarchical, networked
Relational data model
a logical data model proposed by in 1972
it consists of two components:
a logical data structure (relations or tables) for organising data
a set of rules (constraints) for ensuring data integrity
formal definition:
A domain is a set of values. Given a set of domains D1, D2, …, Dn, the Cartesian Product of D1, D2, …, Dn is the set of all tuples
informally, a relation is simply a table
employee name address DoB department
1234 Bloggs, F 10 10/05/1950 Purchasing
3812 Smith, R 92 Coleridge St 12/08/1960 Accounts
7138 Brown, G 2 18/02/1971 Personnel
Terminology
employee name address DoB department
1234 Bloggs, F 10 10/05/1950 Purchasing
3812 Smith, R 92 Coleridge St 12/08/1960 Accounts
7138 Brown, G 2 18/02/1971 Personnel
domain = a set of allowed values
degree = number of columns
cardinality = number of rows
Properties of relations
each relation in a database must have a unique name
student(name, address)
student(first, last)
each column within a table must have a unique name
married(person, person)
rows and columns are unordered
name child gender
name gender child
John male male female Tom
Schema vs. instance
schema = the name of relation together with a set of attributes, e.g.
parent(name, child, gender)
a schema is relatively time–independent
instance = a set of tuples of a relation, e.g.
an instance is time–varying, i.e. the instance of a relation can be different at different times
Constraints
Constraints
not all instances of a schema are valid
e.g. adding (Fred, Tom, female) into the following table will cause a contradiction
to ensure that an instance of a schema is valid, we introduce constraints (rules), e.g.
if tuples have the same name,
then they must have the same gender
name child gender
Types of constraints
the major types of integrity constraint are:
domain constraint
entity integrity
referential integrity
it is also possible to add ad–hoc “check” constraints
Domain constraint
all values that appear in a column of a relation must be taken from the same domain
a domain definition can specify:
allowed values
supplier(SID, name, city)
attribute domain
SID 5 characters
name 25 characters
city {Cardiff, London, Bath}
SID name city
Primary keys
a set of attributes form a key for a relation if there are no two different tuples having the same values with respect to these attributes
question: Is (ID, name) a key?
ID name address
S10203 Mary 123 High Street
S14234 Fred 456 Broadway
S10223 Mary 123 High Street
formally, let R be a relation
a key for R is a subset of attributes K of R such that
uniqueness – no two distinct tuples of R have the same value for K
irreducibility – no proper subset of K has the uniqueness property
if K satisfies property 1, but not 2, we call it superkey, e.g. (ID, name)
we normally indicate a key by underlining it, e.g.
student(ID, name, address)
More about keys
it is possible to have multiple keys for the same relation
one is chosen as primary key and others are called candidate keys
keys are defined for a relation schema, not for a particular instance!
defining a correct key is the designer’s responsibility
name child gender
name appears to be unique in this instance, but this could change when we add new tuples name is not a key!
identify the primary keys for the following tables:
module(module#, title, credit)
takes(student#, module#, mark)
Entity integrity
every relation must have a primary key and a primary key cannot have null values
null values are the values that we enter into a relation to represent the data we do not know
ID name address phone fax
S101 John 123 Street 778899 334455
Dave 456 Road 112233
violation of entity integrity
null value
(not 0 or space)
Foreign keys
Foreign keys
used to link relations
foreign key
B is a foreign key if:
at any time, each value of B is identical to some value of A
A is a primary key
Foreign keys
supplier(supplier#, name, city)
order(order#, quantity, supplier#)
supplier# is a foreign key in order because it is a primary key in supplier (condition 2)
condition 1 will be enforced at run time
Table 1 and Table 2 need not be different
foreign key need not be part of Table 2’s primary key
Foreign keys
formally…
let R2 be a relation
a foreign key in R2 is a subset of attributes FK of R2 such that
there exists a relation R1 with a primary key PK, and
at all times, each value of FK in R2 is identical to some value of PK in R1
Referential integrity
values of a given foreign key must be either null or match values of the corresponding primary key
Supplier(supplier#, name, city)
Order(order#, quantity, supplier#)
every value appearing in the supplier# column of order must either be a null or a value that matches a value appearing in the supplier# column of supplier
order is the referencing relation
supplier is the referenced relation
Referential integrity
order# quantity supplier#
O-876 200 S123
O-898 400 S124
O-990 200 S123
order# quantity supplier#
O-876 200 S123
O-898 400 S124
O-990 200 S125
supplier# name city
S124 N. Bell Swansea
S345 P. Jones Cardiff
supplier# name city
S124 N. Bell Swansea
S345 P. Jones Cardiff
referential integrity satisfied
referential integrity violated
/docProps/thumbnail.jpeg
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com