Please enable JavaScript.
Coggle requires JavaScript to display documents.
W4 The Relational database Model System(RDMS) - Coggle Diagram
W4 The Relational database Model System(RDMS)
Structure of the relational model
a normalised relation to be a table composed of rows and columns where each cell contains a single value.
A Relational Database is:
a collection of
normalised
relations.
proposed by E.F. Codd in 1970
Major commercial relational database products:
Oracle
DB2 (IBM)
SQL Server
MySQL
Relational Schema: structured description
Represents individual named relations and their attributes;
Organisation of the DB as described by the DB administrator
Describes structure and characteristics of each relation.
table name (keyAttribute1, attribute 2, attribute 3 …..)
student (studentId, lastName, firstName, studentEmail, DOB, street, city, postcode, country)
Relational Terminology
data is logically structured within relations (tables)
relation is a table with columns/attributes and rows/tuples
Each
relation
has a name
Each
attribute
is the name of a column of a relation
Each
tuple
元组 is a row of a relation & contains one value per attribute
R D M terminology cont'd
Degree
*
- number of attributes in a relation.
Cardinality
基数- number of tuples in a relation
Domain
范围 - set of allowable values for one or more attributes
Maximum number of possible relationship occurrences for an entity participating in a given relationship type (0, 1, M).
Properties of Relations (tables)
A relation/table is perceived as a
two-dimensional structure
with rows and columns
Each cell of a relation contains exactly
one atomic (single) value.
Relation names are unique (distinct).
Each attribute (column) has a unique name (within each relation).
Values of an attribute are within the same domain and are of the same data format.
The order of attributes and tuples has no significance.
Each tuple/row is distinct or unique i.e. no duplicate tuples.
Relational Keys
Superkey
An attribute, or set , combination of attributes, that uniquely identifies a tuple (row) within a relation.
Candidate Key
a superkey without any unnecessary attributes
Primary key
candidate key that is selected to uniquely identify rows within a relation.
Alternate key
Candidate候选 keys that are not selected to be primary key.
Composite key
A key consisting of more than one attribute
Foreign key
Attribute or attributes in one relation that matches primary key in another relation.
Secondary key
An attribute or combination of attributes used strictly for data retrieval purposes
Mathematical relations数学关系
combine each record from each initial table with each record of all other initial tables of interest.
( need to combine the rows of two relations in order to relate the rows in one relation to the corresponding rows in another relation.
every row of relation R with every row of relation S
: A = {1, 3} and B = {0, 2, 4}.
A x B = {(1, 0), (1, 2), (1, 4), (3, 0), (3, 2), (3, 4)}
Integrity constraints
Protect the database from becoming:
Inaccurate,
Inconsistent
Incomplete
checked by identifying
Multipicity
entity integrity
See 表格
only applies to Primary Keys
No component of the primary key of a base relation is allowed to accept nulls
Attribute domain constraints
Not null
Data type restrictions
Limit or range checks
Completeness checks
Format or template checks
Check digit
Set membership
Master file reference
Record checks
Completeness
Reasonableness
referential intergrity
See 表格
foreign key may have either a null entry
Required data
. cannot hold ‘nulls’
of “unknown” for an attribute
Value that is not yet available
Deal with Incomplete or exceptional data
Not the same as zero or blank
Null is the absence of a value (no value))
general constraints
Updates to entities may be controlled by general constraints.
“real world” transactions
e.g.DreamHome has a rule that prevents a staff member from managing >100 properties
advantages of view
