Database Design and Development

Question 1

What is a surrogate key?

Answer 1

‘made up’ key to uniquely identify each record

Question 2

How can you validate in a data dictionary?

Answer 2

length, range, restricted choice, existing fieldname from tablename table

Question 3

Code a new database and table with each variable type and a PK and FK

Answer 3

CREATE DATABASE name;

CREATE TABLE name (
	fieldname1 VARCHAR(20),
	fieldname2 INT,
	fieldname3 FLOAT,
	fieldname4 DATE,
	fieldname5 TIME,

PRIMARY KEY(fieldname),
FOREIGN KEY (fieldname) REFERENCES table fieldname,

);

Question 4

Describe an entity occurrence diagram

Answer 4

Illustrates the relationship between the entity occurrence between two related occurrences in an entity. Helps to identify the cardinality of the relationship that exists between the two entities.

Question 5

How to draw an entity occurrence diagram

Answer 5

Tall ovals, headings and lines —> shows relationship

Question 6

Describe an entity relationship diagram

Answer 6

Used to summarise the relationship between two or more entities.

• name of each entity
• name of relationship
• the cardinality (one-to-one, one-to-many, many-to-one, many-to-many)
• show participation (mandatory = -, optional = o)
• show type (weak = depends on 1+ strong entity to exist, primary key is partially formed from pk of owner entity. strong = independent, their pk uniquely identifies each occurrence within the entity)

Question 7

How to draw an entity relationship diagram

Answer 7

Labelled rectangles, labelled line, crows feet for many