3. Relational Model - 3marks

Question 1

What is the relational model (background and four basic concepts)?

Answer 1

Background: 
•	Introduced by E.F. Codd in 1970. 
•	Many DBMS products based on this model (but few completely implement it). 
•	Based on sound theoretical foundation with a simple and uniform data structure: the Relation 
•	Based on mathematical set theory. 
Four basic concepts: 
•	Relations 
•	Domains 
•	Attributes 
•	Tuples

Question 2

A relation is (informally):

Answer 2

• a set of records

* similar to a table with columns and rows

Question 3

What is the main construct for representing data in the relational model?

Answer 3

A relation

Question 4

A Schema is…

Answer 4

• the meta-data, or data describing data

* specified during database design, and is not expected to change frequently

Question 5

An Instance is

Answer 5

• the data in the database at a particular time

* created during data updates and change frequently

Question 6

A domain is characterised by….

Answer 6

• a set of atomic values (ie: indivisible) (eg: the domain ‘Name’ has the set { Albert, Anna, …. Zoe }
• it’s specified data type or format

Question 7

Domain data types of formats could include…

Answer 7

• integers
• real numbers
• fixed or variable length character strings
• date
• time stamp
• currency
• sub-range from a data type, e.g. 1 ≤ Grade ≤ 7
• enumerated data type, e.g. {‘Male’, ‘Female’}

Question 8

An attribute is linked to a domain in that it is…

Answer 8

• the name of a role played by some domain in the relation named

Question 9

Does the same attribute name imply the same domain?

Answer 9

• No. Two attributes could be called ‘code’ from different domains: eg: Subject.CODE and Lecturer.CODE.

Question 10

Does a different attribute name imply a different domain?

Answer 10

• No. a different attribute name does not necessarily imply different domain (Domains for EID and SID can be the same, but the attribute names must differ.

Question 11

A tuple is…

Answer 11

• n ordered list of n values
• where each value vi (1  i  n) is an element of the corresponding domain of attribute Ai or a special value called “null”
• Usually shown as a row in DB.

Question 12

Relations are sets of tuples. Do they have an order in a database?

Answer 12

• Not mathematically and practically - order is irrelevant.

* However, the physical storage has some partial ordering.

Question 13

Relational database design is (one sentence) the process of:

Answer 13

• capturing the semantics of an application, and translating it into a relational database schema

Question 14

What are some consequences of poor relational database design?

Answer 14

• Poor design can lead to redundant information in tuples and update anomalies.
• Can also result in inability to represent information and consequently, loss of information.
• Conceptual modelling, and other formal techniques, can assist a designer in obtaining good design characteristics.

Question 15

Where are integrity constraints defined?

Answer 15

On the database schema.

Question 16

What are the four integrity constraints enforced by a DBMS?

Answer 16

1. Key (uniqueness) Constraint
2. Entity Integrity
3. Referential Integrity
4. Domain Constraint
   Acronym to remember: KERD

Question 17

Key (aka uniqueness) Constraint implies:

Answer 17

• No two tuples can have the same value for their primary key attributes

Question 18

Entity Integrity constraints are:

Answer 18

• Primary key values CAN NOT be null.

* In a composite key, NO PART of the KEY can be NULL (eg: store ID and product ID)

Question 19

Referential Integrity constraints are:

Answer 19

• Foreign key must reference a value which exists
• Tables need to be connected correctly through primary keys and foreign keys.
• No database will have more than one table

Question 20

Domain Constraints are:

Answer 20

Every attribute in the relational model comes from some domain.
• No value from one domain can be put into another (eg: text in numerical).
• Can not put multiple values (must be atomic)

Question 21

A Primary Key is:

Answer 21

The attribute or combination of attributes that uniquely identifies a row or record in a relation.

Question 22

A superkey is:

Answer 22

a combination of attributes that can be uniquely used to identify a database record.
NB: A table might have many super keys.

Question 23

Minimal Super key (or just minimal key) is:

Answer 23

a superkey with no redundant attributes.

(EG: RegID uniquely identifies a vehicle, ManufacturerName uniquely identifies a manufacturer, to uniquely identify a car you need both it’s Make and Model.
However multiple car manufacturers can be founded in the same year, meaning YearFounded is not a superkey for manufacturer and so cannot be a minimal key. )

Question 24

A Candidate Key is:

Answer 24

The fields or combination of fields that could be used as a primary key are known as candidate keys or alternate keys.

Question 25

A Foreign Key is:

Answer 25

an attribute or combination of attributes in a relation whose value match a primary key in another relation.
• The table in which foreign key is created is called a dependent (child) table.
• The table to which foreign key is refers is known as parent table.

Question 26

Other general ‘structural’ integrity constraints could include:

Answer 26

Semantic Constraints
• “The salary of an employee should not exceed the employee’s supervisor’s
salary”
• “The maximum number of hours that an employee can work on a project is 56”
Transition Constraints
• “The salary of an employee can only increase”
These are often implemented in a constraint specification language
Eg: (SQL3) using triggers and assertions

Question 27

Why is enforcement of integrity constraints important to a DB?

Answer 27

It ensures that the database remains consistent

Question 28

If a database is changed or updated, can integrity constraints be violated?

Answer 28

No.