Fundamentals of databases Flashcards
Shown below is the design of a database used to store data about TV channels and their owners (e.g. BBC1 and BBC2 are both owned by the BBC. ITV1 and ITV2 are both owned by ITV).
Channel (OwnerID, ChannelID, ChannelNumber, Name)
Programme (ProgrammeID, Title, Genre, ChannelID)
Owner (OwnerID, Name, Country)
State the name of one relation from the design.
Channel
OR
Programme
OR
Owner (1)
(Refuse if any other detail (e.g. attributes) included.)
Shown below is the design of a database used to store data about TV channels and their owners (e.g. BBC1 and BBC2 are both owned by the BBC. ITV1 and ITV2 are both owned by ITV).
Channel (OwnerID, ChannelID, ChannelNumber, Name)
Programme (ProgrammeID, Title, Genre, ChannelID)
Owner (OwnerID, Name, Country)
State the name of one attribure from the design.
- OwnerID
- ChannelID
- ChannelNumber
- Name
- ProgrammeID
- Title
- Genre
- Country
(Allow in the format Channel.ChannelID but otherwise refuse if any other detail included.)
Shown below is the design of a database used to store data about TV channels and their owners (e.g. BBC1 and BBC2 are both owned by the BBC. ITV1 and ITV2 are both owned by ITV).
Channel (OwnerID, ChannelID, ChannelNumber, Name)
Programme (ProgrammeID, Title, Genre, ChannelID)
Owner (OwnerID, Name, Country)
State the name(s) of the most suitable attribute(s) for a primary key in the Channel relation.
ChannelID
Shown below is the design of a database used to store data about TV channels and their owners (e.g. BBC1 and BBC2 are both owned by the BBC. ITV1 and ITV2 are both owned by ITV).
Channel (OwnerID, ChannelID, ChannelNumber, Name)
Programme (ProgrammeID, Title, Genre, ChannelID)
Owner (OwnerID, Name, Country)
State the name of an attribute which is acting as a foreign key and the name of the relation in which it has been used as a foreign key.
Channel.OwnerID OR Programme.ChannelID (2)
1 mark for identification of foreign key
1 mark for correct identification of relation
(Longer, written answers are more likely than the shorter format used here and are perfectly acceptable.)
A letting agent uses a database to keep track of its landlords along with the properties and tenants. A tenancy is agreed when a tenant has a contract to stay at a particular property. Historic records of tenancies are also stored in the database.
The definition of four relations in the database are show below:
Landlord (LandlordID, FirstName, LastName, ContactNumber, Email)
Property (HouseNum, StreetName, Town, PostCode, PropertyType, LandlordID)
Tenant (FirstName, LastName, ContactNumber, Email)
Tenancy (TentancyID, TenantFirstName, TenantLastName, StartDate, Duration, MonthlyRent, SecurityBondAmt)
The Tenant relation uses a composite key. Explain what is meant by a ‘composite key’ and why it had been used in this case. (3 marks)
Two or more attributes/fields (1) used together to uniquely identify a record (1)
Used here because it is possible for two different tenants to have the same first name or same last name (1)
NB: It is still possible for two different tenants to have the same first AND last names, so this does not represent the most ideal solution.
A letting agent uses a database to keep track of its landlords along with the properties and tenants. A tenancy is agreed when a tenant has a contract to stay at a particular property. Historic records of tenancies are also stored in the database.
The definition of four relations in the database are show below:
Landlord (LandlordID, FirstName, LastName, ContactNumber, Email)
Property (HouseNum, StreetName, Town, PostCode, PropertyType, LandlordID)
Tenant (FirstName, LastName, ContactNumber, Email)
Tenancy (TentancyID, TenantFirstName, TenantLastName, StartDate, Duration, MonthlyRent, SecurityBondAmt)
It has been suggested that the database is not fully normalised and that a new relation, Postcode, could be introduced to solve the problem.
Explain what is meant by the term ‘normalised’. (2-5 marks)
- The data is atomic.
- No attribute depends on a partial element of a composite key.
- No dependencies other than on the key field(s).
- Every attribute is dependent on the key.
- Every determinant is a candidate key.
A letting agent uses a database to keep track of its landlords along with the properties and tenants. A tenancy is agreed when a tenant has a contract to stay at a particular property. Historic records of tenancies are also stored in the database.
The definition of four relations in the database are show below:
Landlord (LandlordID, FirstName, LastName, ContactNumber, Email)
Property (HouseNum, StreetName, Town, PostCode, PropertyType, LandlordID)
Tenant (FirstName, LastName, ContactNumber, Email)
Tenancy (TentancyID, TenantFirstName, TenantLastName, StartDate, Duration, MonthlyRent, SecurityBondAmt)
It has been suggested that the database is not fully normalised and that a new relation, Postcode, could be introduced to solve the problem.
Describe the changes that would need t be made to the database in order to incorporate the new relation. User the same notation that has been used in the design above. (3 marks)
Create a new relation, Postcode:
Postcode(Postcode, StreetName, Town)
Edit relation Property:
Property(HouseNum, Postcode, PropertyType, LandlordID)
Purpose of Postcode relation is clear (can be inferred from the attributes used even if not exactly correct) (1)
Attributes of Postcode, including primary key, correct (1)
Attributes remaining in Property, including composite key, correct (1)
Shown below is the design of part of a database used to manage an online audiobook subscription in which customers can ‘purchase’ on free audiobook per month as long as their subscription is still valid. Each book, purchase and author is identified using a unique numeric code.
Customer (Email, FirstName, LastName, EndDate)
Purchase (PurchaseID, CustomerEmail, BookID, Date)
Audiobook (BookID, Title, AuthorID, Reader, Length)
Author (AuthorID, FistName, LastName)
The length of the Audiobook Alice in Wonderland has been incorrectly entered as 1 hour 59 minutes when it should say 2 hours 59 minutes.
Write the SQL commsnds that are required to record this change in the database.
UPDATE Audiobook (1)
SET Length = 2:59 (1)
WHERE Title = “Alice in Wonderland” (1)
Shown below is the design of part of a database used to manage an online audiobook subscription in which customers can ‘purchase’ on free audiobook per month as long as their subscription is still valid. Each book, purchase and author is identified using a unique numeric code.
Customer (Email, FirstName, LastName, EndDate)
Purchase (PurchaseID, CustomerEmail, BookID, Date)
Audiobook (BookID, Title, AuthorID, Reader, Length)
Author (AuthorID, FistName, LastName)
Write the SQL commsnds that are required to find the title and reader for all of the books by Terry Pratchett.
SELECT Title, Reader
FROM Audiobook
INNER JOIN Author ON Audiobook.AuthorID = Author.AuthorID
WHERE FirstName = “Terry”
AND LastName = “Pratchett”
Analysis (3)
Identifying the tables and attributes needed (1)
Identifying the foreign key relationship as part of the WHERE clause (1)
Identifying the correct condition (FirstName and LastName) (1)
SQL (2)
Correct SQL syntax in 2 or 3 of the four SQL clauses (SELECT, FROM, WHERE, AND) (1)
Fully correct SQL (1)
Refuse answers that include other tables.
Accept answers that use INNERJOIN or OUTERJOIN as correct.
Shown below is the design of part of a database used to manage an online audiobook subscription in which customers can ‘purchase’ on free audiobook per month as long as their subscription is still valid. Each book, purchase and author is identified using a unique numeric code.
Customer (Email, FirstName, LastName, EndDate)
Purchase (PurchaseID, CustomerEmail, BookID, Date)
Audiobook (BookID, Title, AuthorID, Reader, Length)
Author (AuthorID, FistName, LastName)
Write the SQL commsnds that are required to find the title and author’s full name for every book downloaded in Junt 2018, sorted in ascending order by the author’s last name. (7 marks)
SELECT Title, FirstName, LastName
FROM Purchase
INNER JOIN Audiobook Purchase.bookID = Audiobook.bookID
INNER JOIN Author ON Audiobook.AuthorID = Author.AuthorID
WHEREDate BETWEEN 1/6/18 AND 30/6/18
ORDER BY LastName
Analysis (4)
Identifying the tables and attributes needed (1)
Identifying one foreign key relationship as part of the WHERE clause (1)
Identifying the correct condition (Date in June 2018) (1)
Identifying need to sort the results by last name (1)
SQL (3)
Correct SQL syntax in 2 of the five SQL clauses (SELECT, FROM, WHERE, AND, ORDER BY) (1)
Correct SQL syntax in 3 or 4 of the five SQL clauses (SELECT, FROM, WHERE, AND, ORDER BY) (1)
Fully correct SQL (1)
Accept answers that include the Student table, even though this is not necessary.
Accept other forms of selecting a valid date, e.g. Date >= 1/4/18 AND Date <= 30/4/18, use of DATEPART, MONTH or LIKE, as long as they are valid.
Refuse overly simplistic attempts to identify the date, e.g. Date = “May 2018” Q1.
A data set has been classified as Big Data.
Describe the three characteristics that might classify a data set as Big Data. (3 marks)
- Volume: data that is too big to fit on a single server (1)
- Velocity: the speed at which data is generated (1)
- Variety: the differences in the structure OR format/types of data (1)
NB: The 3Vs are excellent to aid recall but answer MUST go into more detail.
A data set has been classified as Big Data.
In order to help process the data effectively, a distributed computer architecutre is used. State the most suitable programming paradigm for handling BigData in a distributed system.
Functional programming
A data set has been classified as Big Data.
State two features support by functional programming that make it easier to process Big Data.
- Data is immutable OR stateless OR one part of a program cannot change the data and therefore impact another part of the program.
- High-order functions OR functions such as Map/Reduce/Fold/Filter can be easily parallelised (to run as part of a distributed system).
- Order of execution is less important/not as strictly defined (due to the immutable nature of the data, which makes it easier to process the data in a distributed system).
Shown below is the design of part of a database used to manage the acts and stages at a music festival. Acts include music, comedy and a range of other entertainment types and may perform multiple times across the fetstival. Different stages have different curfew times dependent onthe type of acts due to perform there and their location withing the festival. Because the festival runs over several days, each booking includes the dat of the week (Friday, Saturday or Sunday). The BookinID and AgentID are made up of unique numeric codes.
Act (Name, ActType, Requirements, SetLength, Agent)
Stage (Name, StageType, Curfew)
Booking (BookingID, ActName, StageName, Day, StartTime)
Agent (AgentID, FirstName, LastName, Mobile, Email)
Complete tjhe following DDL statement to create the Booking relation, including the key field.
CREATE TABLE Booking (
CREATE TABLE Booking(
BookingID INT
ActName VARCHAR (60)
StageName VARCHAR (20)
Day VARCHAR(8)
StartTime TIME
PRIMARY KEY (BookingID)
FOREIGN KEY (ActName) REFERENCES Act(Name)
FOREIGN KEY (StageName) REFERENCES Stage(Name))
OR
BookingID INT PRIMARY KEY
ActName FOREIGN KEY REFERENCES Act(Name)
StageName FOREIGN KEY REFERENCES Stage(Name)
Defining BookingID with a valid data type and as primary key (1)
Defining ActName with a valid data type and as a foreign key, with correct reference (1)
Defining StageName with a valid data type and as a foreign key, with correct reference (1)
Two other valid fields with sensible data types and lengths (if included) (1)
All fields with sensible data types and lengths and no additional fields (1)
Valid data types for BookingID: INT, SMALLINT, MEDIUMINT, INTEGER, any text type
Valid data types for text fields: VARCHAR, CHAR, NCHAR, NVARCHAR, VARCHAR2, NVARCHAR2, TEXT, LONGTEXT, NTEXT, TINYTEXT, MEDIUMTEXT
Day: text field, but also allow DATE, DATETIME, DATETIME2, DATE/TIME, SMALLDATETIME
StartTime: TIME, DATETIME, DATETIME2, DATE/TIME, SMALLDATETIME
Text lengths not necessary but must be sensible if included.
NOTNULL not necessary but allow.
Shown below is the design of part of a database used to manage the acts and stages at a music festival. Acts include music, comedy and a range of other entertainment types and may perform multiple times across the fetstival. Different stages have different curfew times dependent onthe type of acts due to perform there and their location withing the festival. Because the festival runs over several days, each booking includes the dat of the week (Friday, Saturday or Sunday). The BookinID and AgentID are made up of unique numeric codes.
Act (Name, ActType, Requirements, SetLength, Agent)
Stage (Name, StageType, Curfew)
Booking (BookingID, ActName, StageName, Day, StartTime)
Agent (AgentID, FirstName, LastName, Mobile, Email)
State the name of a pimary key use within the database
- Name
- BookingID
- AgentID
(Accept answers in the form Act.Name)
