2 Database modelling

Question 1

Recap: Why the relational database?

Answer 1

Data is stored once and only once. Removes data anomalies

Non loss decomposition- All possible queries of data can be retrieved

Question 2

List and explain the three phases of database design

Answer 2

1. Conceptual- Study your organisation to understand how you can model your data.
2. Logical- Model how data is stored in databases used by software applications. Ensure no information is lost
3. Physical- How data is stored in databases tailored to specific DBMS system

Question 3

Explain role of Data Administrator

Explain role of Database Administrator

Answer 3

Data administrator

• Create/maintain data models
• Planning requirments
• Develop procedures/polocies of data models

Database Adminstrator

• Managaement of physical system
• Monitoring performance
• Secuirity and integrity controls

Question 4

CHEN + UML MODELLING

What is an entitie?

Draw both CHEN & UML versions

Answer 4

An important aspect of a system! Something we need to keep information

Question 5

What is an attribute?

Answer 5

Information which makes up an entitie

At least one identifying attribute or combination of attributes for entitie occurence- UNIQUE

Question 6

What does attribute domain mean?

Answer 6

The possible set of all values

Can share a domain- e.g

Hair colour: brown, black, blonde, white, grey

Question 7

Entitiy occurence -Identifying keys

• Define a candidate key
• Primary key
• Composite key
• Natural key
• Surrogate key

Answer 7

Candidate key- Minimal set of attributes that uniquely identify each occurence of an entity type

Primary key- Candidate key that uniquely identyifs each occurence of an entity type

Composite key- Consists of two or more attributes

Natural key- Identifying key which is derived from the entitys existing attributes

Surrogte key- Artifical key introduced for efficiency. Usually an integer