Part 1 Flashcards
Give a brief explanation of the difference between the rerms information and data.
Information is any kind of knowledge that is interpretable by people in some context. Data is a representaion of information; it has no meaning without a context.
Give a sentence that represents your understanding of the information content of the form.
The cource with the title “Relational Databases: theory and practice” has code M359, cource manager John Sykes and 618 students enrolled on it.
“Students enrolled” on the form is an example of devirred data. Explain what this means in terms of data in a database.
The value for “Student enrolled” is a dirrived data becouse it is calculated from other data in the database.
State the number of occurences of the Supplies relationship shown in Figure 1.
5 ( the number of occurrence lines).
What can you deduce about the degree and participation conditions of the Supplies relationship based on the occurrences given?
The participation of the Supplier in the relationship is optional. An occurence of Supplier may be related to many occurrences of Part via the Supplier relationship.
“The participation of the Supplier in the relationship is optional. An occurence of Supplier may be related to many occurrences of Part via the Supplier relationship.”
draw an E-R diagram showing the Supplies relationship between the two entities typesSupplier and Part.
relation Staff
StaffNumber: StaffNumbers
Name: Names
primary key StaffNumber
relation Computer
Code: Codes
Type: Types
primary key Code
relation IsAssigned
StaffNumber: StaffNumbers
Code: Codes
primary key StaffNumber
foreign key StaffNumber references Staff
alternative key Codes
foreign key Code references Computer
Write down relational algebra epressions;
Each staff member is associated with at least one task
constraint ((project Staff over EmployeeNumber)
difference
(project Task over EmployeeNumber))
isEmpty
A staff member can be associated with a secretarial task only if his/her grade is 2 or above.
constraint
(select Staff join Task where Grade > 1 and TaskType = Secretarial)
is empty
Demonstrate that Vets has no functional dependencies with determinant PersonName and with the right-hand side consisting of a single attribute.
PersonName>Vet? No, because e.g. Barker is associated with two values of Vet.
PersonName>AnimanName? No, because e.g. Ahmad is associated with animals with names Fluffy and Tiger.
PersonName>AnimalType? No, Because e.g. Barker is associated with both a dog and a horse.
Consider the following decomposition of Vets into the relations Vets1 and Animals:
Vets1 alias (project Vets over Vet, PersonName, AnimalName)
Animals alias (project Vets over AnimalName, AnimalType)
Is this a non-lossy decomposition?
The first decomposition gives a relation which can be depicted by the first three columns of the table; the second gives a relation which can be depicted by the last two columns, When we join these two decompositions together, we get the following tuples.
The second tuble depicted was not in the orginal relation, so this is a lossy decomposition - the information that the second tuple is not part of Vets is lost.
Consider the following relation R with functional dependecies as shown;
R(a, b, c, d)
a,b -> c
a,b -> d
a -> c
Is R in third normal form?
R is not in second normal form because of a -> c. ( That is, c is not fully functionally dependent on the primary key.)
So R cannot be in third normal form as it is not even in second normal form.
SELECT b.a1, b1
FROM a,b
WHERE b.a1 = a.a1
AND a2 > 500
SELECT a1, a2
FROM a
WHERE (SELECT COUNT(*)
FROM b
WHERE b.a1 = a.a1)<2
SELECT c2, f
FROM c, (SELECT b2, COUNT(*) AS f
FROM b
GROUP BY b2) AS t1
WHERE b2 = c1
List the membership number, name and date of birth of each member for whom date of birth has been stored
SELECT member_no, name, date_of_birth
FROM member
WHERE date_of_birth IS NOT NULL
Give the membership number and name of each member who is currently borrowing one oe more CDs.
SELECT DISTINCT member.member_no, name
FROM member, loan
WHERE member.member_no=loan.member_no
For each member of the library, list his or her membership number and the number of CDs that he or she is currently borrowing. Your solution should show 0 as the number of CDs borrowed for members who are not currently borrowing CDs.
SELECT member.member_no, COUNT(cd_name) AS
number_borrowed
FROM member LEFT OUTER JOIN loan
ON member.member_no=loan.member_no
GROUP BY member.member_no
The administrator of the CD library has defined a procedure make_loan. The definition contains the declaration: make_loan(IN member_nos, IN cs_names)
where member_nos and CD_names are the domains of membership numbers and CD name, respectively. When make_loan is called, a row is added to the loan table containing the membership number of the member borrowing the CD, the name of the CD and the return date, which is calculated by the procedure.
Give two reasons why using this procedure may be preferable to wrking directly with the loan table.
Access conrol: A user can be given access to the procedure, but not have any access privilages to the underlying tables.
Ease of use: The procedure means that the processing (calculation of the return date, and statements relating to the specific tables) cab be hidden from the user.
In addition to the make_loan procedure, a function items_borrowed is define which returns the number of the CD that a member has borrowed. Its signatures is
items_borrowed (IN member_nos) RETURNS INTEGER
A member is allowed to borrow up to 3 CDs. The member with membership number m48 wishes to borrow “Best of Bach” and “Magic of Mozart”.
You are required to write a transaction which either successfully completes with the member borrowed more than 3 CDs. You should use the procedure make_loan and the function items_borrowed to answer this question.
BEGIN
CALL make_loan(‘m48’, ‘Best of Bach’);
CALL make_loan(‘m48’, ‘Magic of Mozart’);
IF items_borrowed(‘m48’) > 3
THEN ROLLBACK WORK;
ELSE COMMIT WORK;
END IF;
END
Entity-relationship diagram
Entity types
Additional constraints
Assumptions
Limitations
Entity types
Employee(StaffNo, Names_)_
Book(ISBN, Title, Author, Cost)
Reservation(StaffNo, ISBN, DateReserved)
Additional consttaints
c. 1 Reservation is a weak entity tyoe dependent on the entity type Employee. So each value of the StaffNo attribute in the entity type Reservation must be the same value as the StaffNo attribute of the Employee instance to which the Reservation entity type is related by the relationship Makes(a consequence of weak-strong entity types).
c. 2 Reservation is a weak entity type dependent on the entity type Book. So each value of the ISBN attribute in the entity type Reservation must be the same value as the ISBN attribute of the Book onstance to which the Reservation entity type is related by the relationship IsForA (a consequence of the weak-strong entity types.)
Assumptions - None
Limitations - None
Give three reasons why it may be necessary to restructure a database after it has been built and installed.
- Correcting errors or omissions in the database design and/or implementing that remained undetected after acceptance testing.
- Optimising performance.
- Satisfying new and changing requirements.
Give two reasons why denormalisation is not usually performed unless a performance need arises.
Denormalisation results in duplicated information which (1) may require more disk space to store (2) may result in insertion, amendment and deletion anomalies if the duplicated information needs to be updated.
What do (i) OLAP and (ii) OLTP systems facilitate within an organisation?
i. OLAP systems enable decision makers to gain insight into an organisation’s data from different, and multiple, perspectives.
ii. OLTP systems support the day-to-day operations of an organisation, such as purchasing, inventory, manufacturing, banking, payroll and accounting.
Brefly wsplain what is meant by the term ‘time variant’ for a data warehouse, and describe the main consequence of this in terms of multi-dimensional model.
The term ‘time variant’ means that historical data is recorded. Time is always one of the dimensions of a multi-dimensional model.
Give one reason why it is customary to implement a data warehouse seperately from the operational systems that are the source of its data.
Processing conflicts (queries vs updating).
Different organisation of data.
No historical data in operational systems.
Multiple sources/formats.
Briefly describe a document type definition and a document type declaration, making clear the distinction between the two terms.
A document type definistion is a set of markup declerations that define a document type and its structure.
A document type decleration is an instruction to assosiate XML within document type definision.
Explain why neither of these two terms (document type declaration, document type definition) is relevant to the XML data types as defined by SQL/XML.
The XML data type as defined by SQL/XML has a value that is a well-formed (i.e. parsable) XML document, which does not require a document type declaration or associated DTD; if one is included, its not in use.
Java host variables are different from host variables in other programming languages for which embedded SQL can be used becouse they:
a. are not specified in a declare section.
b. do not require the prefix: in embedded SQL statements.
c. cannot be used in host language statements.
a. are not specified in a declare section.
JDBC implicitly establishes a cursor after executing the method:
a. getConnection
b. createStatement
c. executeQuery
c. executeQuery
To optain a value from the current wor of a JDBC result set, you have to use a get method with:
a. the column number.
b. the data type of the column in the database.
c. the data type of the result.
c. the data type of the result.
If the prepareStatement methid is executed for an SQL query containing a parameter marker, the query can be executed:
a. imediately.
b. after a value has been set for the parameter marker.
c. after a database connection is estalished.
b. after a value has been set for the paremeter marker.
An object-relational mapping tool enables a Java program to access a database without writing SQL by:
a. using mapping schema to translate classes to tables.
b. using Java procedures stored in the database.
c. using a pre-compiler to translate Java to the native DBMS interface.
a. using a mapping schema to translate classes to tables.
Data Administrators, Database Administrators, Database Developers, Application Developers, End Users
a) … : in many cases will neither know, nor care, that a database is involved in meeting their needs.
b) … : will be responsible for translating the logical database design into a physical realisation within a specific DBMS product, balancing performance characteristics and physical storage option.
c) … : have responsibility for implementing the data use policies and conceptucal models as well as the day-to-day running of the database systems.
d) … : have a role that includes understanding the business requirements of the organisation and overseeing the planning and development of new data systems and the maintenance and control of existing systems.
e) … : are tasked with building or customising user processes (programs and utilities, report generators and query tools) to meet the specific requirements of the end users.
a) End users
b) Database Developers
c) Database administrators
d) Data Administrators
e) Application Developers
Consider the following part of a conceptual data model for annual shows, the
clubs that host them and the sponsors who fund the shows (Note that the E-R
diagram is incomplete).
a.)The relationship ‘FundedBy’ is not shown on the entity-relationship diagram
above. Complete the entity-relationship diagram above by showing the
relationship, representing the degree and participation conditions required to
meet the description given below:
Each AnnualShow is funded by zero or one Sponsors.
Each Sponsor supplies funding for one or more AnnualShows
AnnualShow(Year, Theme, Venue, SponsorName)
Consider the following part of a conceptual data model for annual shows, the
clubs that host them and the sponsors who fund the shows (Note that the E-R
diagram is incomplete).
b) Briefly explain why each of the following scenarios cannot be represented by
the conceptual data model for annual shows.
(i) The West Midlands show will be co-hosted by clubs in Birmingham,
(ii)A sponsor has agreed to sponsor the 2015 Milton Keynes annual show,
but the organisers are still looking for a club to host it in that year.
Solihull and Coventry.
(i) Because an attribute can only have 1 value, also IsHostFor states that each annualShow must have 1 club.
(ii) Value cannot be NULL, isHostFor states that each annual show must have 1 club.
(i) Give one reason why this table is not a relational table.
(ii) Represent the same information in a relational table and give the
primary key for the associated relation.
(i) The first row has two values under StaffManaged. A relational table can only have one value per attribute (only atomic values are allowed)/
(ii)
S1 Oxford S3
S1 OxforSaved S4
S2 Oxford S1
S5 London S2
primaryKey (EmployeeNumber, Location)
Complete the following relational representation so that it corresponds to the E-R
model above.
You may assume that suitable domains, StaffIds, ModuleCodes, StaffNames and
ModuleNames have already been defined.
Relation AcademicEditor
SfattId: StaffIds
StaffName: Staff Names
ModuleCode: Module Codes
Primary Key StaffId
{mandatory parcipation in Edits}
foreign key (ModuleCode) reference Module
{Edits 1:1}
Alternate key(ModuleCoede)
Relation Module
ModuleCode: ModuleCodes
ModuleName: Module Names
Primary key ModuleCode
Relation Writer
StaffId: staff ids
StaffName: staff names
ModuleCode: module codes
Primary key StaffId
{mandatory participation in Writers}
foreign key ModuleCode References Module
(a) Give the table that is returned by the following query, using the data in
Table 1.
SELECT part_no, supplier_name, stock_level
FROM part
WHERE price >= 75
(b) Give the table that is returned by the following query, using the data in Table 1.
SELECT part_no
FROM part
WHERE stock_level > (SELECT SUM(stock_level)
FROM part WHERE price >=75)
(c) Give the table that is returned by the following query, using the data in
Table 1.
SELECT supplier_name, COUNT(*) as how_many
FROM part
WHERE part_name <> ‘gearbox’
GROUP BY supplier_name
HAVING MIN(stock_level) = 50
(a)
p22 Newton 25
p35 Electra 30
(b)
p04, p07
(c)
suppliers_name | how_many
Newton | 1
Write SQL queries to answer the following requests.
(a) List the part number and part name for all parts whose supplier is located in
London.
(b) Give the total monetary value of all stock where stock levels are above 50.
(c) Give the names of suppliers who supply more than one part.
(a)
SELECT part_no, part_name
FROM part a, supplier b
WHERE location =’London’ AND a.supplier_name = b.supplier_name
(b)
SELECT SUM(price*stock_level) AS stock_monetary_value
FROM part
GROUP BY part_no, stock_level
HAVING stock_level >50
(c)
SELECT supplier_name
FROM (SELECT supplier_name, COUNT(supplier_name) AS sn_count
FROM part
GROUB BY supplier_name) AS supp_names
WHERE supp_names.sn_count>1
(a)
CREATE TABLE supplier
(supplier_name VARCHAR(25) NOT NULL,
location VARCHAR(25) NOT NULL,
PRIMARY KEY (supplier_name),
(b)
CREATE TABLE part
(part_no CHAR(3) NOT NULL,
part_name VARCHAR(25) NOT NULL,
supplier_name VARCHAR(25) NOT NULL,
stock_level INTEGER NOT NULL,
price INTEGER NOT NULL,
PRIMARY KEY (part_no),
(a)
CONSTRAINT supplier_parts
CHECK supplier_name IN (SELECT supplier_name FROM part);
(b)
CONSTRAINT fk_supplier_name
FOREIGN KEY supplier_name REFERENCES supplier;
CONSTRAINT part_no_valid
CHECK(part_no(SUBSTR(VALUE, 1, 1)= ‘p’) AND
(CAST(SUBSTR(VALUE,2,2) AS INT) BETWEEN 0 AND 99)));
The delivery company wants to record the estimated distance and estimated
journey time for each delivery from a depot to a customer.
In the space below produce a new conceptual model so that the additional
requirements of recording estimated distance and estimated journey time are
shown.
C.1 Delivery is a weak entity type dependent on Depot. So, each value of IdCode in the entity type Delivery must be the same value as the IdCode of the Depot Instance to which the Delivery entity type must related by the relationship Deliver(a consequence of weak-strong entity types).
C.2 Delivery is a weak entity type dependant on Customer. So each value of Reference of the entity type Delivery must be the same as value as the Reference of the Customer instance …
(a) Explain what is meant by denormalisation. What reason is usually given in
order to justify it?
(b) List the three types of anomalies that may arise after updating an
unnormalised table.
(a) Denormalisation is the process of merging tables that are associated by l:m relationships and the reason for this is usually to speed up the query process
(b) insertion, deletion and amendment
The following is a well formed tree of XML-elements containing some data about
a DVD library. This tree represents some details of a single DVD and the
recordings on the DVD (chapters).
Write down headings for relations that are capable of holding the same data as
the dvd_library XML structure. You should use two relational headings and
underline their primary keys, to allow the data for the details of several DVDs and
their chapters to be stored and recreated. Show how the above data would
appear in your relations.
(You can assume that the order of chapters on a DVD in the XML tree structure
does not need to be recreated).
dvd(catalogue_no, title, artist)
Chapters (chapter_no, title, dvd_cat_no)
dvd
catalogue_no | title | artist
B550361 | Spirited away | Hayao Miyazaki
chapters
chapter_no | title | dvd_cat_no
1 | midle of nowhere | B550361
2 | It’s just a dream | B550361
3 | Finding work | B550361
4 | Meeting Yubaba | B550361
Complete the following sentences by inserting appropriate words into the
underlined spaces, one per space, by choosing words from the following
list. The same word may occur more than once in the sentences. Not all
the words listed are used in the sentences.
Words: security, logical, many, distributed, four, three, two, mobile,
schema, data, index, storage, space, user, management,
representation, external, model, server, transfer, exchange,
interaction, mapping, property, replicated.
(i) The _____________-schema architecture is a general model of
database representation that places importance on the separation of
the ______________, _____________ and _____________
schemas.
(ii) In this model, data independence is achieved by _______________
between schema representations. Logical data independence is
provided by the _______________ between _____________ and
_______________ schemas.
(iii) In the _______________-schema architecture there can be many
_______________ schemas, but only a single ______________
and _______________ schema defined for a database.
(i) three, logical, storage, external
(ii) mapping, mapping, logical, external
(iii) three, external, logical, storage
(a) Give a description of the relationship, Signs, in Figure 1. Your answer
should include a description of the degree and participation information for
the relationship.
(b) What is the purpose of the Additional Constraints section of a Conceptual
Data Model?
(c) Illustrate your answer to part (b) by writing a suitable Additional Constraint
to ensure that, for each of the players signed by a team, the team’s age
limits (implied by the attribute names chosen for the entity types in
Figure 1) are appropriately enforced.
(a)
Team can be signs by zero, one or many Playesr
Player can be signs for zero or one Team
(b)
The additional constrains section records any conditions that, if not enforced, would permit the existance of inconsistent data in the database. Only those constraints that cannot be shown elswhere in E-R model are listed here.
(a) Write down the relational representation of the part of the conceptual data
model shown in Question 2, Figure 1, using the relational theory taught in
Block 2 of the course.
You can assume that the domains TeamNames, Ages, PlayerNames,
RegistrationNumbers and Dates have all been declared so that you can
use them but do not need to declare them
(b) The situation modelled in Question 2, Figure 1, is to be changed so that
the entity type Team has mandatory participation in the relationship Signs.
To represent this new constraint, write down a relational expression that
could be added to the answer you gave in part (a) above.
Relation Represention
relation Team
Name: TeamNames
MinimumAcceptedAge: Ages
MaximumAcceptedAge: Ages
Primary key Name
relation Player
RegistrationNumber: RegistrationNumbers
Name: PlayerNames
DateOfBirth: Dates
Primary key RegistrationNumber
Relation Sings
Name: TeamNames
RegistrationNumber: RegistrationNumbers
Primary key RegistrationNumber
foreign key ReegistrationNumber References Player
foreign key Name References Team
(b)
CONSTRAINT ((project Team over Name)DIFFERENCE (project Sings over Name)) isEmpty
Explain why the Sample 1 data is not a valid representation of values of
the relation Company.
Because the Manager column on the table depicted does not have values for each row, a realation must have atomic values for each attribute.
(b) Give the table that is returned by the following query.
SELECT budget_code, COUNT(DISTINCT staff_number) AS c
FROM project p, assignment a
WHERE p.project_code = a.project_code
GROUP BY budget_code
[1]
(c) Express the following query in English, and give the table that the query
returns.
SELECT project_code
FROM project p
WHERE person_hours > (SELECT SUM(hours)
FROM assignment a
WHERE a.project_code =
p.project_code)
(b)
budget_code | c
c43 | 2
f19 | 1
(c)
List the project codes where the person hours is greater than the total number of hours logged in assigment for that project
(a) The owner of the tables executes the following statement, when the tables
contain the values shown in Table 1 in the Appendix.
INSERT INTO availability VALUES(‘4’, 45)
Give the table that is then returned by the query:
SELECT *
FROM v
(b) The owner of the tables executes the following statement, when the tables
contain the values shown in Table 1 in the Appendix.
UPDATE v
SET person_hours=15
WHERE project_code=’d’
Give the table that is then returned by the query:
SELECT *
FROM v
and the table that is returned by the query:
SELECT *
FROM project
(a)
project_code | person_hours
d | 60
(b)
b | 40
a | 30 | c43
b | 40 | c43
c | 20 | c43
d | 60 | f19
(b) The same company keeps records of deliveries undertaken by their van
drivers (typically several a day). Each delivery is to a single customer.
The following information needs recording for each delivery:
• the van registration number (RegistrationNo);
• the driver’s staff number (StaffNo);
• the date (Date);
• the time started (TimeStarted);
• the time completed (TimeCompleted); and
• the customer order number (CustomerOrderNo).
Write down all possible identifiers of the Delivery entity type.
- Need for distinct rows - duplicate in row 1 & 7
- every collumn must have a value - missing value in Occupier
(i) Write down a table representing the relation C difference A.
[1 mark]
(ii) Write down a relation B and a relational operator op such that
A op B = C.
(i)
Student | Cource | ExamMark | CourceWorkMark
Ian Smith | Interaction Design | 58 | 76
(ii)
UNION =
(i)
relation Book
BookId: BookIds
Title: BookTitles
ExpectedPublicationDate: Dates
(ii)
relation Chapter
ChapterNumber: ChapterNumbers
BookId: BookIds
DateDue: Dates
(iii)
relation Writer
WriterRef: WriterRefs
Name: Names
(i)
Primary Key BookId
{mandatory participation of Book in ConsistOf relationship}
CONSTRAINT((project Book OVER BookID) DIFFERENCE (Project Chapter over BookID)isEmpty)
(ii)
Primary Key (ChapterNumber, BookID)
{mandatory participation of ChapterNumber in Writes relationship}
foreign key BookID Reference Book
(iii)
Primary Key WriterRef
(a)
SELECT SUM(y) as total_y, SUM(b) as total_b
FROM alpha JOIN omega ON y = b
[1 mark]
(b)
SELECT x, COUNT(a) as how_many
FROM alpha LEFT OUTER JOIN omega ON x = d
GROUP BY x
[2 marks]
(c)
SELECT *
FROM alpha
WHERE EXISTS (SELECT *
FROM omega
WHERE x = d
AND y = b )
(a)
total_y | total_b
500 | 550
(b)
x | how_many
Bath | 1
Hull | 2
York | 1
Goole | 0
(c)
Hull | 200 | granite
For each of the following requests, write an SQL query that answers the request.
(a) List the number of every script which contains an answer to question 2
where the mark for that answer is less than 25.
[1 mark]
(b) List the number and total mark of every script whose total mark (the sum of
the marks of the two questions answered) is 50 or more.
[2 marks]
(c) List the name of every marker who has marked at least one question 4.
(a)
SELECT Script_no
FROM marked
WHERE question = 2
AND mark < 25
(b)
SELECT Script_no, SUM(mark) AS total_mark
FROM marked
GROUP BY script_no
HAVING total_mark >49
(c)
SELECT DISTINCT name
FROM marked_by, marked
WHERE quiestion = 4
AND marked_by.script_no = marked.script_no
ALTER table employee
ADD CONSTRAINT mandatory_participation_at_the_employee_end
CHECK (EXISTS(SELECT *
FROM project
WHERE employee.employee_no = project.employee_no))
ALTER table project
ADD CONSTRAINT relationship_works_on
FOREIGN KEY (employee_no) References employee
(a) Briefly explain why the E-R model only allows each borrower to take a
particular book out on loan overnight on one occasion.
[1 mark]
(b) Revise the model to allow each borrower to take a particular book out on
loan overnight on more than one occasion but ensures that a book can be
on loan overnight to only one borrower at a time.
[2 marks]
(c) Explain why Loan is a weak entity type in the original model fragment.
(a)
If there were two occurrences of identical BorrowerNo and BookNo it would invalidate the model as each row in the table must be unique
BorrowerNo and BookNo must be unique and this is not the case if the book is on loan overnight on more than one occasion.
(b)
Loan(BorrowerNo, BookNo, Date)
(c)
The weak entity type Loan cannot exist without the existance of the entity type Borrower and Book.
The following is a well formed tree of XML-elements containing some data about
CD (compact disc) library. This tree represents some details of a single CD and
the recordings on the CD (tracks).
B550361
Walton Symphony Orchestra
1
2
3
4
Write down headings for relations that are capable of holding the same data as
the cd_library XML structure. You should use two relational headings and
underline their primary keys, to allow the data for the details of several CDs and
their tracks to be stored and recreated. Show how the above data would appear
in your relations.
(You can assume that the order of tracks on a CD in the XML tree structure does
not need to be recreated).
CD(CatalogueNo, Title, Artist)
Track(CatalogueNo, TrackNo, Title)
CatalogueNo | Title | Artist
B550361 | Beethoven’s 5th.. | Walton Symphony Orchestra
CataloqueNo | TrackNo | Title
B550361 | 1 | 1st
B550361 | 2 |2nd
B550361 | 3 | 3rd
B550361 | 4 | 4th
(i) “Office O32 is currently the location for a single employee E243.”
IS CONSISTENT IS NOT CONSISTENT
[1 mark]
(ii) “All of the company’s current employees, except employee E243, are
located in the same office O31.”
IS CONSISTENT IS NOT CONSISTENT
[1 mark]
(iii) “From tomorrow employee E243 will be located in O32 on Monday,
Tuesday and Friday, but will be located in office O31 on Wednesday
and Thursday.”
IS CONSISTENT IS NOT CONSISTENT
(a)
(i) -is consistent
(ii) - is not consistent
(iii) - is not consistent
(a) Entity-relationship diagram
[4 marks]
(b) Entity types
(b)
X(a1, a2)
Y(a3, a4)
Z(a7, a8)
(i) Write down the relational expression above replacing operator1 and
operator2 by two set operators introduced in Block 2.
[2 marks]
(ii) Write down a relational table for Relation3.
[1 mark]
(iii) Write down a relational table for Relation4.
(a) - no value for office
(b)
(i)
operator1 - difference, operator2 - union
(ii)
Con | Pat | Hos
C3 | P4 | H1
(iii)
Con | Pat | Hos
C2 | P5 | H2
For each of the following SQL queries, give the table that the query returns.
Note: in your answer include the table headings.
(a)
SELECT country, spent
FROM budget
WHERE received > 50000;
[1 mark]
(b)
SELECT gift_id, donor_email, appeal_code
FROM appeal, donation
WHERE appeal.country = donation.country
AND donor_email IN (‘mrf3@easymail.com’,
‘jbc@yes.co.uk’);
[2 marks]
(c)
SELECT gift_id, donor_email, appeal_code
FROM appeal RIGHT OUTER JOIN donation
ON appeal.country = donation.country
WHERE donor_email IN (‘mrf3@easymail.com’,
‘jbc@yes.co.uk’);
[
(a)
country | spent
Belka | 240000
Costa Luna | 60000
(b)
gift_id | donor_email | appeal_code
D5 | jbc@ | A35
(c)
gift_id | donor_email | appeal_code
d5 | jbc | A35
d1 | jbc | NULL
d4 | mrf | NULL
(a) Give SQL queries that can be used to answer the following requests:
(i) How many donations have been made by credit card?
[1 mark]
(ii) Give the email address of every donor who has made one or more
donations without specifying a destination country.
[2 marks]
(b) Rewrite the following query so that it uses a subquery in the FROM clause
instead of using a HAVING clause.
SELECT donor_email, COUNT (*) AS num
FROM donation
GROUP by donor_email
HAVING COUNT(*) >= 2;
[
(a)
(i)
SELECT COUNT (*) AS num
FROM Donation
WHERE Payment_type = ‘credit card’
(ii)
SELECT DISTINCT Donor_email
FROM Donation
WHERE Country IS NULL
(b)
SELECT *
FROM (SELECT Donor_email, COUNT(*) AS num
FROM Donation
GROUP BY Donour_email) AS s
WHERE num >1
(a) Which solution would you recommend? Briefly justify your answer. (You
should identify one relevant difference between a trigger and a procedure.)
Note: You are not expected to implement the proposed solutions.
(b) What are the differences in the firing condition for the trigger defined, in one
case, as BEFORE UPDATE and, in the other case, as AFTER UPDATE? For
solution 2, would BEFORE UPDATE be more appropriate than AFTER
UPDATE? Briefly justify your answer.
(a) Procedure has to be invoked by a user. And trigger is invoked automaticly on update.
(b)
The trigger timing is specified AFTER that the triger is fired after the insertion into the table is complete. BEFORE - so that the trogger is fired before the insertion into the table.
I would like to use AFTER UPDATE because audit_limits record all value including current one. If audit table recorded only historical data, then we could use BEFORE UPDATE which copy previous value before it is overwritten by current value.
(a) Briefly explain why the conceptual data (E-R) model given in the above
figure will fail to meet the requirements if a person can be qualified for a
number of different roles at the same level in the same sport, and a person
can be qualified in the same role but at different levels for the same sport.
For example:
Kathy could be qualified as an international lineperson and timekeeper
for hockey
Peter could be qualified as both a regional and international referee for
football.
[2 marks]
(b) Revise the model to overcome the limitation identified in part (a).
[1 mark]
(c) Explain why Qualification is a weak entity type in the model fragment.
(a) The primary key of the Qualification entity type forces a person and sports combination to be unique meaning a given person can only be associated with a given sport.
(b) Qualification(PersonId, SportName, Role, Level)
(c)
Occurences within the Qualification entity type are dependent on the existance of relevant occurences in the Person and SportsDiscipline entity types. Qualififacation cannot exist without participarion from these two entity types.
a) State if each of the following statements about the items and their
locations in display cases is TRUE or FALSE or UNKNOWN (that is their
truth cannot be determined from the information given).
(i) A display case must be the location for more than one artefact.
[1]
(ii) All artefacts must be located in display cases.
[1]
(iii) There is a display case that is not the location for any artefacts.
(b) The following sentences describe the relationship between Cars and their
Owners:
Each Car is owned by exactly one Owner. Each Owner owns one or more Cars.
(i) - false
(ii) - false
(iii) - false
(b)
Question 3
Using as few entity types as possible, write down the E-R diagram and the
entity types A, B and C (underlining the identifiers of the entity types) that are
represented by the following relational model.
relation A
A1: DomA1
A2: DomA2
primary key A1
relation B
B1: DomB1
B2: DomB2
A1: DomA1
primary key B1
alternate key A1
{relationship R1}
foreign key A1 references A
relation BtoC
{relationship BtoC}
B1:DomB1
C1:DomC1
primary key (B1, C1)
foreign key B1 references B
foreign key C1 references C
relation C
C1: DomC1
C2: DomC2
primary key C1
A(A1, A2)
B(B1, B2)
C(C1, C2)
(a)Identify the two possible candidate keys of the relation R, using the
information given in the above table.
(b)
In a certain organisation, each member of staff is identified by a unique
staff number. Consider the following three relations, in which you may
assume that attributes with the same name are defined over the same
domain and that attributes with different names are defined over different
domains.
Secretary(StaffNumber, SecretaryName)
Technician(StaffNumber, TechnicianName)
Salesman(StaffNumber, SalesmanName)
Write down a relational constraint definition that will enforce the constraint
that no member of staff can be both a technician and a secretary.
(a) Attribute1, Attribute3
(b)
CONSTRAINT((project Secretary OVER StaffNumber) intersection (project Technical OVER StaffNumber)) isEmpty
(a)
SELECT *
FROM t
WHERE d2 = ( SELECT MAX(c2)
FROM s)
[1]
(b)
SELECT t.d1, COUNT(c1) AS num
FROM t LEFT OUTER JOIN s ON t.d1 = s.d1
GROUP BY t.d1
[2]
M359/S 9 [TURN OVER]
(c)
SELECT d1
FROM t
WHERE d2 IN (SELECT c2
FROM s
WHERE t.d1 = s.d1)
(a)
d1 | d2
bar | 3
baz | 3
(b)
t.d1 | num
foo | 1
bar | 2
baz | 2
ram | 0
(c)
d1
baz
For each of the following requests, write an SQL query that answers the
request.
(a) List the number of every script which contains an answer to question 2
where the mark for that answer is less than 25.
[1]
(b) List the number and total mark of every script whose total mark (the sum
of the marks of the two questions answered) is 50 or more.
[2]
(c) List the name of every marker who has marked at least one question 4.
(a)
SELECT script_num
FROM marked
WHERE question =’2’ AND mark < 25
(b)
SELECT script_num, SUM(mark) AS total_mark
FROM marked
GROUP BY script_num
HAVING total_marks >49
(c)
SELECT DISTINCT name
FROM marked m, marked_by mb
WHERE m.script_num = mb.script_num AND question = ‘4’
A partial implementation contains the following SQL (all relevant SQL domains
are defined, but only the constraints shown have been defined on the tables
and columns at this stage of development):
CREATE TABLE stacker (
staff_no staff_numbers,
grade grades,
PRIMARY KEY (staff_no) )
CREATE TABLE warehouse (
warehouse_id warehouse_ids,
capacity warehouse_capacities,
address addresses,
PRIMARY KEY (warehouse_id) )
It is decided to implement the WorksAt relationship with a foreign key. Give
the necessary ALTER TABLE statement(s) to add an appropriate column to
the relevant table and add any other required constraints. You may assume
that the tables currently contain no data.
ALTER TABLE Stacker ADD warehouse_id warehouse_ids
ALTER TABLE stacker ADD CONSTRAINT works_at FOREIGN KEY (warehouse_id) References warehouse ON DELETE RESTRICT
ALTER TABLE warehouse ADD CONSTRAINT mandatory_stacker_in_warehouse CHECK (NOT EXISTS ((SELECT DISTINCT warehouse_id FROM warehouse) EXEPT (SELECT DISTINCT warehouse_id FROM stacker))
(a) Are the above code fragments examples of ODBC calls, Embedded SQL,
or JDBC calls?
[1]
(b) Write down the names of the Host Variables that appear in the above
statements.
[1]
(c) For each of the following statements state if it is true for ODBC, Objectrelational
Mapping, neither or both (underline the correct answer in each
case):
(i) This does not require the application programmer to know SQL.
ODBC Object-relational mapping Neither Both
(ii) In some circumstances this allows a database schema to be
automatically generated to meet the data storage requirements of
an application program.
ODBC Object-relational mapping Neither Both
(iii) This can be used interactively by the end-user to access a
database.
ODBC Object-relational mapping Neither Both
(a) - embedded sql
(b) - code name value
(c)
(i) - ODBC
(ii) - Neither
(iii) - ODBC
(a) The E-R diagram is not complete. There are two relationships (CanUse and
WorksIn) whose degree and participation conditions are not given above.
Complete the diagram by representing the degree and participation
conditions given below:
Each employee can use one or many computers.
Each computer can be used by one or many employees.
Each employee works in exactly one office.
Each office has zero, one or more employees who work in it.
[4 marks]
(b) Is the following statement consistent or inconsistent with the given model?
No computer is located in office number 107.
(Circle your answer below)
CONSISTENT INCONSISTENT
(b) - Consistent
Give a relational representation of the E-R model by defining two relations,
Department and Employee and any additional constraint(s).
You may assume that the domains DepartmentNames, StaffNames,
DepartmentalOffices and StaffNumbers have already been defined and that the
values of the attributes DepartmentName, StaffName, DepartmentalOffice and
StaffNumber are from these domains respectively.
Relation Department
DepartmentName: DepartmentNames
DepartmentalOffice: DepartmentalOffice
StaffNumber: StaffNumbers
primary key DepartmentName
{relationship HeadedBy is 1:1}
alternate Key StaffNumber
{mandatory participation of Departmenr in the ConsistOf relationship}
CONSTRAINT((project Deparment OVER DepartmentName) difference (project Employee OVER DepartmentName)) isEmpty
relation Employee
StaffNumber: StaffNumbers
StaffName: StaffNames
DepartmentName: DepartmentNames
primary key StaffNumber
{mandatory participation of Employee in the ConsistOf relantionship}
foreign key (DepartmentName) Reference Department
(a) Why does the table not represent a relation?
[1 mark]
(b) Write down the heading of a relation Vet which conveys the same sort of
customer information as the table above. Do not forget to underline the
primary key and include a brief justification of your choice of primary key.
[1 mark]
(c) Write down a relational expression on the relation Vet to find the name and
address of each family which has (at least) one dog registered before
1/1/2000.
[
(a)
need a singe value (in First Registered)
(b)
Vet(FamilyName, Address, PetType, PetNames, FirstRegistered)
(c)
project(select Vet where FirstRegistered <‘Jan1, 2000’ and PetType =’dog’) OVER Familyname, Address
(a) Match is not in second normal form (2NF). Use a non-loss decomposition to
decompose Match into two relations, both of which are in 2NF. You should
justify why each is in 2NF.
[2 marks]
(b) Define what is meant by a relation being in Boyce-Codd normal form
(BCNF).
[1 mark]
(c) Consider the relation with heading R(a, b, c, d).
The only functional dependencies with irreducible determinants in R are
F1: a→ b F2: a → d F3: c → a
Explain clearly why R is in BCNF.
[
(a)
HomeTeam (HomeTeamName, GroundAddress)
Match_2 (HomeTeamName, DATE, AwayTeamName, Result)
Both are 2NF as neither HomeTeamName nor Date determine any other attributes.
(b)
R is BCNF if any only when every one of its dependencies X->Y have at least one of following
- X as superkey of R
- X-> is a trival FD (FD trival if Y is subset of X)
(c)
In F1 and F2, the determinant is the primary key in F3 the determinant is an alternate key Each irreducible determinant of a non-trival FD is a candidate key and so R is in BCNF.
(a)
SELECT SUM(y) as total_y, SUM(b) as total_b
FROM alpha JOIN omega ON x = d
WHERE x = ‘Hull’;
[1 mark]
Question 6
M359 October 2011 TURN OVER 9
(b)
SELECT COUNT(a) as how_many
FROM omega LEFT OUTER JOIN alpha ON b = y;
[2 marks]
(c)
SELECT *
FROM alpha
WHERE x IN (SELECT d
FROM omega
WHERE y = b );
(a)
total_y | total_b
400 | 400
(b)
how_many
4
x | y | z
Hull | 200 | granite
For each of the following requests, write an SQL query that answers the request.
(a) List the cities where at least one traveller has visited since 2009 and that
visitor has stated nightlife to be its best feature.
SELECT city
FROM trip
WHERE year > 2008 AND best_feaure = ‘nightlife’
(b) For each city, and each best_feature for that city, count the times the
travellers have rated that as the city’s best feature. When applied to the
trip table your query should return the following data:
(c) For each traveller who has visited London in 2009, report the
traveller_id and number of cities visited by that person in 2010.
(b)
SELECT city, best_feature, COUNT(best_feature) AS count_rated
FROM trip
GROUP BY best_feature, city
(c)
SELECT travel_id, COUNT(year) AS number_cities_visited_in_2010
FROM trip
WHERE traveller_id IN (SELECT travel_id FROM trip WHERE city = ‘London’ and year = 2009)
GROUP BY traveller_id, year
HAVING year = 2010
(a) (i) Write an SQL view definition called nightlife that when evaluated
will return, for those trips where the reported best feature was
nightlife, the traveller_id, city and year of travel under the
column names who, where, and when, respectively.
[2 marks]
(a) (ii) Is it possible to insert rows into the base table by inserting values into
your nightlife view? Briefly justify your answer.
[2 marks]
(b) Briefly state one purpose of the SQL WITH CHECK OPTION clause.
(a) (i)
CREATE VIEW nighlife (who, where, when) AS SELECT traveller_id, city, year FROM trip WHERE best_feature = ‘Nightlife’
(a) (ii)
No, trip states that the collumn best_feature cannot be NULL and this would be the case because in the view nightlife, this collumn is omitted.
(b)
Prevents rows from being inserted into the view which then do not appear in the view itself.
The analyst is given a new requirement: to incorporate information about actual
hotels. For each hotel it is important to know: the hotel’s name, the hotel’s email
address, the city it is in, the star-rating, and the Hotel chain it is part of. You are
asked to incorporate this information by decomposing the m:n relationship and
including an additional entity type.
Revise the E-R model fragment above (that is, re-draw the above E-R diagram
and give the additional entity type) so that the amended model fragment is
consistent with the new requirement.
(
Hotel(CityName, ChainName, HotelName, EmailAddress, StarRating)
Additional constraints
C.1 Hotel is a weak entity type dependent on City, So, each value of CityName in the entity type Hotel must be the same value as the Name of the City instance to which the Hotel entity type is related by the relationship IsIn ( a consequence of weak-strong entity types)
C.2 Hotel is a weak entity type dependent on HotelChain. So, each value of ChainName in the entity type Hotel must be the same value as the CHainName of the HotelChain instance to which the Hotel entity type is related by the relationship BelongsTo ( a consequence of weak-strong entity types)
Assumptions
a.1. A HotelChain doesn’t have two hotels with the same name in the same city.
Draw an entity-relationship (E-R) diagram and give the entity types described by
the following:
Each Teacher can teach on zero, one or many Courses.
Each Course is taught by exactly one Teacher.
Each Course is identified by its subject name and reference number so that, for
example, (Geography, R13) , (Geography, R11) and (History, R11) are different
courses. We are also interested in the course duration and cost. Each teacher has
a unique employee number and we are interested in the teacher’s appointment
date, and the highest qualification they hold.
Entity-relationship diagram
Entity types
Entity-relationship diagram
Teacher(EmployeeNo, apptDate, HighestQual)
Cources( Name, ReferenceNi, Durration, Cost)
Entity-relationship diagram
Entity types
Entity types
A(x1, x2)
B(x3, x4)
C(x5, x6)
(a) the names of all the customers living in Manchester.
[1 mark]
(b) those towns where there are customers but no shops.
[1 mark]
(c) the codes and names of those customers who live in the same town as one
or more shops.
(a)
project(SELECT Customer WHERE Town = ‘Manchester’) OVER CustomerName
(b)
(project Customer over Town)difference (project shop over town)
(c)
project ShopCustomer allias ((project Shop over Town) join (project Customer over CustomerCode, customerName, town)) over customerCode, customerName
For each of the following SQL queries, give the table that the query returns.
(a)
SELECT *
FROM camera
WHERE price > 380
[1 mark]
(b)
SELECT order_id, customer_id, price
FROM order, lens
WHERE lens_code = code
AND camera_code IN (‘A50’, ‘P200’)
[2 marks]
(c)
SELECT order_id, customer_id, price
FROM order LEFT OUTER JOIN lens
ON lens_code = code
WHERE camera_code IN (‘A50’, ‘P200’)
(a)
code | level | price
A50 | am | 400
P200 | prof | 1200
(b)
order_id | customer_id | price
18 | c32 | 800
(c)
order_id | customer_id | price
18 | c32 | 800
27 | c91 | NULL
32 | c91 | NULL
(a) Give SQL queries that can be used to answer the following requests.
(i) How many cameras with the code A30 have been ordered?
[1 mark]
(ii) Give the identifier of every customer who has ordered at least one
camera without a lens.
[2 marks]
(b) Rewrite the following query so that it uses a subquery in the FROM clause,
instead of a HAVING clause.
SELECT camera_code, COUNT(*) AS num
FROM order
GROUP BY camera_code
HAVING COUNT(*) >= 2
[
(a) (i)
SELECT COUNT(*) AS num_a30_ordered
FROM order
WHERE camera_code = ‘a30’
(ii)
SELECT DISTINCT Customer_id
FROM order
WHERE lens_code IS NULL
(b)
SELECT DISTINCT camera_code
FROM (SELECT camera_code, COUNT(*)AS num FROM order) AS t
WHERE t.num >1
(a) Why does the following statement not change the contents of t?
INSERT INTO v2
VALUES (‘o’, 4, ‘ban’)
[1 mark]
(b) What effect does executing the following statement have on table t and view
v1? If executing the statement does not cause any changes, explain why not.
UPDATE v1
SET c = 4
WHERE a = ‘j’
[2 marks]
(c) What effect does executing the following statement have on table t and view
v2? If executing the statement does not cause any changes, explain why not.
UPDATE v2
SET c = 1
WHERE b = ‘n’
(a)
Because there is no collumn and hence value for the primary key of t interview v2
(b)
T will be updated with new value but v1 will not display it because of its criteria c<3
(c)
neither t or v2 will update, v2 says c>1 and it’s using the WITH CHECK OPTION which will inhibit the base table and view being updated if criteria are not met.
(a) After a review it is decided to change the conceptual model to capture the
following requirement:
Each actor may act in a number of different series, and for each one he or
she has a contract with a start date and end date. An actor can only have one
contract for any particular series.
Modify the model above to reflect the revised requirement. (You need only
show those parts of the model changed or added.)
[3 marks]
(b) Identify the weak entity type(s) in the complete revised model, and state their
dependencies.
(a) Contract(StageName, SeriesTitle, startDate, endDate)
(b) Episod and Series are both weak entitty types.
Write down headings for relations that are capable of holding the same data as
the patient_records XML structure. You should use two relational headings
and underline their primary keys, to allow the data for the details of several
patients and their treatments to be stored and recreated. Show how the above
data would appear in your relations.
(You can assume that the order of the treatments in the XML tree structure does
not need to be recreated).
patient (id, name, gender)
Treatment(Id, StartDate, Reason, Doctor_id)
Patient
ID | Name | Gender
p68 | Monroe | F
Treatment
ID | StartData | Reason | Doctor_id
p68 | 11/11/2009 | pain relief | 110
p68 | 9/3/2009 | low temp | 131
