Database Design & Development Flashcards
What is an ‘end-user’ requirement?
An end user requirement is the tasks the people who are going to be using the database expect to be able to perform.
What is a ‘functional requirement’?
A functional requirement are the functionalities a database must be able to perform in order to be fit for purpose.
What is an entity-relationship diagram?
An entity-relationship diagram is a visual representation of the entities in a database system, and the relationship that exists between two or more of them.
What is a primary key?
A primary key is a field used to uniquely identify every record in a specific database.
A primary key is unique to a database and therefore is an identifier of a specific database.
What is a foreign key?
A foreign key is a primary key from a separate database that is being used in another database.
These are used within the relational database model.
How can a relationship be drawn in an entity-relationship diagram?
“Crow’s feet” are used to signify that an entity relationship is one to many. E.g. One resort will have many hotels.
What is a data dictionary?
A data dictionary is something that is used to indicate the properties of each attribute needed to define the entities.
What are the properties used within a data dictionary?
The properties used within a data dictionary are:
The entity name,
The attribute name,
Whether it is a primary key, foreign key, or neither,
What attribute type it is (text, number, date, time, Boolean),
What attribute size it has,
What type of validation is present.
What are the four types of validation?
The four types of validation are:
A presence check: to check whether data is there,
Restricted Choice: to restrict the input that comes into a database.
Field length: to check whether an input meets the valid field length.
Range check: to check whether data is within a certain range.
Design a search query to list the hotel name, star rating, town, resort-type, and check-in times of all hotels which allow check-in before 15:00.
These details should be displayed from highest rating to lowest rating, and hotels with the same star rating should be listed in alphabetical order of town name.
Answer is attached below:
How would you construct an SQL SELECT statement?
SELECT column1, column2, …
FROM table_name
WHERE condition;
How would you construct an SQL INSERT statement?
INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);
How would you construct an SQL UPDATE statement?
UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;
How would you construct an SQL DELETE statement?
DELETE FROM table_name
WHERE condition;
