Part 1 Flashcards
(113 cards)
1
Q
Give a brief explanation of the difference between the rerms information and data.
A
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.
2
Q
Give a sentence that represents your understanding of the information content of the form.
A
The cource with the title “Relational Databases: theory and practice” has code M359, cource manager John Sykes and 618 students enrolled on it.
3
Q
“Students enrolled” on the form is an example of devirred data. Explain what this means in terms of data in a database.
A
The value for “Student enrolled” is a dirrived data becouse it is calculated from other data in the database.
4
Q
State the number of occurences of the Supplies relationship shown in Figure 1.
A
5 ( the number of occurrence lines).
5
Q
What can you deduce about the degree and participation conditions of the Supplies relationship based on the occurrences given?
A
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.
6
Q
“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.
A
7
Q
A
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
8
Q
Write down relational algebra epressions;
Each staff member is associated with at least one task
A
constraint ((project Staff over EmployeeNumber)
difference
(project Task over EmployeeNumber))
isEmpty
9
Q
A staff member can be associated with a secretarial task only if his/her grade is 2 or above.
A
constraint
(select Staff join Task where Grade > 1 and TaskType = Secretarial)
is empty
10
Q
Demonstrate that Vets has no functional dependencies with determinant PersonName and with the right-hand side consisting of a single attribute.
A
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.
11
Q
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?
A
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.
12
Q
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?
A
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.
13
Q
SELECT b.a1, b1
FROM a,b
WHERE b.a1 = a.a1
AND a2 > 500
A
14
Q
SELECT a1, a2
FROM a
WHERE (SELECT COUNT(*)
FROM b
WHERE b.a1 = a.a1)<2
A
15
Q
SELECT c2, f
FROM c, (SELECT b2, COUNT(*) AS f
FROM b
GROUP BY b2) AS t1
WHERE b2 = c1
A
16
Q
List the membership number, name and date of birth of each member for whom date of birth has been stored
A
SELECT member_no, name, date_of_birth
FROM member
WHERE date_of_birth IS NOT NULL
17
Q
Give the membership number and name of each member who is currently borrowing one oe more CDs.
A
SELECT DISTINCT member.member_no, name
FROM member, loan
WHERE member.member_no=loan.member_no
18
Q
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.
A
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
19
Q
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.
A
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.
20
Q
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.
A
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
21
Q
Entity-relationship diagram
Entity types
Additional constraints
Assumptions
Limitations
A
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
22
Q
Give three reasons why it may be necessary to restructure a database after it has been built and installed.
A
- Correcting errors or omissions in the database design and/or implementing that remained undetected after acceptance testing.
- Optimising performance.
- Satisfying new and changing requirements.
23
Q
Give two reasons why denormalisation is not usually performed unless a performance need arises.
A
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.
24
Q
What do (i) OLAP and (ii) OLTP systems facilitate within an organisation?
A
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.
25
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.
26
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.
27
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.
28
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.
29
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.
30
JDBC implicitly establishes a cursor after executing the method:
a. getConnection
b. createStatement
c. executeQuery
c. executeQuery
31
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.
32
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.
33
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.
34
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
35
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)
36
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.
37
(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 Oxfor[Save](https://www.brainscape.com/decks/3294238/cards/138268525/edit#)d S4
S2 Oxford S1
S5 London S2
primaryKey (EmployeeNumber, Location)
38
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
39
(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
40
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
41
(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)));
42
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 ...
43
(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
44
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
45
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
46
(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.
47
(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
48
The Hospital scenario used in the course materials has a relational
representation that includes the following relational headings:
Nurse(StaffNo, NurseName, WardNo)
Supervises(StaffNo, Supervisor)
Ward(WardNo, WardName, NumberOfBeds)
The attributes StaffNo of Nurse and Supervises and the attribute Supervisor
are defined using one domain. The attributes WardNo of Nurse and Ward are
defined using one domain. All other attributes have their own domains.
Note: You only require the information given in the above relational headings
and the domain descriptions to answer this question.
(a) Write down a relational algebra expression which will evaluate to give the
name of the nurse and the ward number for each nurse in the Nurse
relation.
[1]
(b) Write down a relational algebra expression which will evaluate to give the
staff number, ward name and the number of beds in that ward for each
nurse in the Nurse relation.
[2]
(c) Write down a relational algebra expression which will evaluate to give the
name of any nurse who is a supervisor.
(a)
PROJECT Nurse OVER NurseNumber, WardNo
(b)
PROJECT (Nurse JOIN Ward) OVER StaffNumber, WardName, NumberOfBeds
(c)
PROJECT(Supervises JOIN(Nurse rename(StaffNo as Supervisor))) OVER NurseName
49
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.
50
(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
51
(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
52
(a) Name the two ways in which a table may be fragmented in a distributed
database, and for each one give the operation required to reconstruct the
original table.
(b) In the global optimisation stage of query optimisation for distributed
databases, what must be minimised?
(c) Who or what are the two main users of the metadata that should be stored
in the metadata repository associated with a data warehousing system?
(a)
Horizontally - UNION
Vertical - JOIN
(b)
Transfer of data
(c)
The decision makers in the organisation, and the data warehousing system itself.
53
(a) The main organisation of an XML document is expressed in terms of
(i) entities
(ii) attributes
(iii) elements
[1]
(b) Valid XML must
(i) contain processing instructions
(ii) conform to a DTD or schema
(iii) be organised as a tree structure
[1]
(c) The SQL/XML function
(i) XMLELEMENT
(ii) XMLTABLE
(iii) XMLFOREST
is not an XML publishing function. [1]
(d) Shredding an XML document is a process that
(i) destroys the document
(ii) changes the order of the data content of the document
(iii) extracts data from the document and inserts it into database tables.
[1]
(e) In SQL/XML, the XML data type has values that must be
(i) parsed XML
(ii) stored as character strings
(iii) valid XML.
(a) - ellements
(b) - conform to a DTD or schema
(c) - XMLTABLE
(d) - extracts data from the document and inserts it into database tables.
(e) - stored as character strings
54
Complete the following sentences by choosing the most appropriate term from
the list of three given below that sentence (circle or mark the chosen term
clearly).
(a) _________ data refers to data that will continue to be stored even when the
applications creating and using the data are no longer running.
static | persistent | updatable
[1 mark]
(b) Databases have many rules, usually known as \_\_\_\_\_\_\_\_\_\_\_\_\_, that define
the valid data that can be stored in the database.
concerns | restrictions | constraints
[1 mark]
(c) OLAP systems include software to perform \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_.
transaction processing | data mining | maintenance
[1 mark]
(d) The __________ schema is concerned with data as it is presented to user
processes.
external | storage | logical
[1 mark]
(e) A domain of discourse describes the information and data that is
\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ an organisation.
relevant to | owned by | trusted by
(a) - persistent
(b) - constraints
(c) - data mining
(d) - external
(e) - relevant to
55
(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.
56
1. Need for distinct rows - duplicate in row 1 & 7
2. every collumn must have a value - missing value in Occupier
57
(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 **=**
58
(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
59
(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
60
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
61
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
62
(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.
63
(a) What is database denormalisation? Why is it sometimes desirable to
denormalise a database? How can denormalisation result in inconsistent
data?
[3 marks]
(b) Give two reasons why it may be necessary to restructure a database after it
has been installed and accepted by the client.
(a)
Database denormalisation is the process of merging tables. It can improve the efficiency of the queries by reducing the number of joins that are needed to obtain data, It can result in duplicated data which can cause insertion amendmenmt and deletion anomalies.
(b)
Meet changes in requirements To optimise the performence if data retrival.
64
(a)One approach applicable to managing distributed data is a replication
system. Name two other distinct approaches to managing distributed data.
[2 marks]
(b) A method of supporting transactions for multi-servers is two-phase commit.
Describe what happens in each of the two phases. What is the condition for
the second phase to begin?
(a)
Client multiserver system
Distributed database
(b)
First phase all locations involved in transaction sent a request asking to prepare to commit.
Second phase each sent a request to complete the commit.
The second phase starts once all locations have replied that they are ready to commit,
65
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
66
Complete the following sentences by choosing the most appropriate term from
the list of three given below that sentence (circle or mark the chosen term
clearly).
(a) A database is a collection of ____________________ structured data.
static /persistent/ updatable
[1 mark]
(b) OLTP systems include software to perform \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_.
transaction processing /data mining /productive maintenance
[1 mark]
(c) A domain of discourse describes the information and data that is
\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ an organisation.
relevant to/ owned by /trusted by
[1 mark]
(d) The ________________ schema in the three schema architecture is
concerned with how data is physically represented within the file system of a
DBMS.
logical /storage /external
[1 mark]
(e) COMMIT and ROLLBACK are SQL commands that support
\_\_\_\_\_\_\_\_\_\_\_\_\_\_ management.
change /transaction /security
(a) - persistent
(b) - transaction processing
(c) - relevant to
(d) - storage
(e) - transaction
67
(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
68
69
(a) Entity-relationship diagram
[4 marks]
(b) Entity types
(b)
X(_a1,_ a2)
Y(_a3,_ _a4)_
Z(_a7,_ a8)
70
(a) Define what is meant by the transitivity property of functional dependencies.
[1 mark]
(b) Consider the relation with heading R(a, b, c, d, e).
(i) Given that the functional dependency F1: c → (a, b) holds in this
relation, show that the functional dependencies
F2: c → d
F3: c → e
both hold in the relation.
[2 marks]
(ii) The set { F1, F2, F3, F4, F5, F6 } is the complete set of non-trivial
functional dependencies with irreducible determinants on R,
where F1 … F6 are as follows:
F1: c → (a, b) F2: c → d F3: c → e
F4: (a, b) → c F5: (a, b) → d F6: (a, b) → e
Show that R is in BCNF.
[
a)
If A determines B and B determines C, then A must determine C
(b)
(i)
c --\> a,b (F1)
a,b --\> d and e (primary key)
(ii)
To be in BCNF any determinant must be a candidate key; (a,b) is a candidate key since it's the primary key; and the other determinant (c) is a candidate key since it can determine all attributes
71
(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
72
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
73
(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
74
(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.
75
(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.
76
(a) Explain what is meant by database reorganisation; and database
restructuring. Relate your answers to the three-schema architecture.
Note: a one sentence answer in each case should be sufficient.
[2 marks]
(b) Give three reasons why it may be necessary to restructure a database after
it has been installed and accepted by the client.
(a) Database reorganisation involves changes to the storages schema which specifies how the DB is stored and accessed. Database restructuringinvolves changes to the logical schema to mayby safisfy any new requiremnts.
(b) To correct anu errors in design, to incorporate any changes to requirements and to optimise retrival performance.
77
(a) One approach applicable to managing distributed data is a client-multiserver
system. Name two other distinct approaches to managing distributed data.
[2 marks]
(b) A method of supporting transactions for multi-servers is two-phase commit.
Describe what happens in each of the two phases. What is the condition for
the second phase to begin?
(a)
Replicated systems and distributed database
(b)
1st phase; all involved are sent a request to prepare to commit, when all have replied with 'ready to commit' the 2nd phase begins
2nd pase; all involved are requested to complete the commit, if any one location involved doesn't reply or replies with cannot commit the commits fails.
78
(a) Give one advantage and one disadvantage of dynamic SQL against static
SQL.
[2 marks]
(b) Give two advantages of scripting languages in database applications.
[2 marks]
(c) Give one advantage of prepared statements.
(a) Advantage: your program ca accept user input to define the SQL statement(s) to execute
Disadvantage: performance is not as static SQL
(b) Simplified customisation for the user ease of text processing.
(c) more efficient
79
Complete the following sentences by choosing the most appropriate term from
the list of three given below that sentence.
(a) A database is a collection of ____________________ data.
(i) static
(ii) simple
(iii) structured [1]
(b) OLAP systems include software to perform \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_.
(i) transaction processing
(ii) data mining
(iii) productive maintenance [1]
(c) A domain of discourse describes the information and data that is
\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ an organisation.
(i) relevant to
(ii) owned by
(iii) trusted by [1]
(d) The ________________ schema in the three schema architecture is
concerned with a representation of the data that is independent of how it
is stored and how it is presented outside of the DBMS.
(i) logical
(ii) physical
(iii) external [1]
(e) The file-based approach to managing data is likely to suffer from
\_\_\_\_\_\_\_\_\_\_\_\_\_\_.
(i) impedance mismatch
(ii) inaccurate data
(iii) unproductive maintenance
(a) - structure
(b) - data mining
(c) - relevant to
(d) - logical
(e) - unproductive maintenance
80
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)
81
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)
82
(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
83
(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
84
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'
85
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))
86
(a) During the database design task we may choose to replace a composite
primary key with a surrogate key. Give one advantage and one
disadvantage of employing surrogate keys.
[2]
(b) What is the purpose of adaptive maintenance? What does adaptive
maintenance involve?
[2]
(c) Database restructuring may involve modifying the columns of a table.
Under what circumstances does modifying the columns of a table become
difficult and need to be done with care?
[
(a) Advantage: improved indexing whcih will speed up searching
Disadvantage: the surrogate key is often unrelated to the data in the rows
(b)
adaptive maitenance involves restructuring the SB fro reasins of changes and additions to requirements of the DB
(c)?
87
(a) What is the main aim of replication systems?
[1]
(b) Briefly describe the two main roles of a replication server?
(a)
The main aim of replication systems is to keep the processing locally, which minimises the number of remote connections and redueces the danger of remote site or connections failure.
(b)
1. to receive or collect updates from the local DBMS and pass them onto the remote replication servers.
2. to receive or collect updates from remote replication server and update the local DBMS.
88
(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
89
Complete the following sentences by choosing the most appropriate term from
the list of three given below that sentence (circle or mark the chosen term
clearly).
(a) _________ data refers to data that will continue to be stored even when the
applications creating and using the data are no longer running.
static /persistent /updatable
[1 mark]
(b) Databases have many rules, usually known as \_\_\_\_\_\_\_\_\_\_\_\_\_, that define
the valid data that can be stored in the database.
concerns /restrictions /constraints
[1 mark]
(c) Data is said to be ________________ if it is appropriate for the use to which
it is being put.
accurate /complete/ relevant
[1 mark]
(d) The __________ schema is concerned with data as it is presented to user
processes.
external /storage/ logical
[1 mark]
(e) _____________________ are SQL commands that support transaction
management.
COMMIT/ROLLBACK | INSERT/UPDATE | CREATE/DROP
(a) - persistent
(b) - constraints
(c) - relevant
(d) - external
(e) - commit / rollback
90
(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
91
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
92
(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
93
(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
1. X as superkey of R
2. 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.
94
(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
95
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'
96
(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
97
(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.
98
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.
99
(a) Explain what is meant by denormalisation. What reason is usually given in
order to justify it?
[2 marks]
(b) List the three types of anomalies that may arise after updating an
unnormalised table.
(b) Insertion, Deletion and ammendment
100
(a) What is an enterprise data warehouse? What is a data mart?
[2 marks]
(b) Give three considerations to take into account when building an enterprise
warehouse system.
(a)
The Data mart is a collection of information of use to a specific group within an organisation, or information about a specifiv product.
(b)
- Checking for inconsistencies and inaccurate values
- Accessibility of the data for example how can it be accessed from different locations
- removing duplicates
101
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)
Treatments ( _patient\_id, start\_date,_ reason, doctor\_id)
Patient
id | name | gender
p68 | Monroe | F
Treatment
patient\_id | start\_date | reason | doctor\_id
p68 | 11/01/09 | pain relief | 110
p68 | 4/3/09 | low temp | 131
102
(a) Complete the following sentences by choosing the most appropriate term
from the list of three given below that sentence (circle or mark the chosen
term clearly).
(i) Unproductive maintenance is a consequence of \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_
between the application processes and the stored data the application
uses.
inaccuracy / data independence / data dependence
[1 mark]
(ii) \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_differs from data inside a running
application because it will continue to be stored when the application
terminates.
Normalised data /Persistent data /Duplicated data
[1 mark]
(iii) ____________________________ database systems use a logical
data structure in which all data appears in a tabular form, and can be
manipulated by mathematical operations that produce results that are
also in tabular form.
Object-oriented /Relational/ Network
[1 mark]
(b) Briefly state the purpose of the Data Analysis stage in the waterfall model of
database development.
[
(a)
(i) - data independence
(ii) - persistent data
(iii) - relational
(b)
103
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)
104
Entity-relationship diagram
Entity types
Entity types
A(_x1,_ x2)
B(_x3_, x4)
C(_x5,_ x6)
105
(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
106
(a) (Patient, Clinic) is the only candidate key of R.
[3 marks]
(b) R is in second normal form (2NF).
(a)
Patient has no unique tuple, neither do clinic or doctor or (patient, doctor) or (clinic, doctor) therefore teh candidate key for R is (patient, clinic)
(b)
As we have identified patient, clinic is the only candidate key, it must be the primary key. As the doctor is fully functionaly dependent on the primary key the relation is in 2NF
107
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
108
(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
109
(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.
110
(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.
111
(a) Operational maintenance ensures that a database continues to satisfy the
informational requirements of an enterprise after it has been installed. What
are the two processes involved in operational maintenance?
[2 marks]
(b) Give three reasons why it may be necessary to restructure a database after it
has been installed and accepted by the client.
(a)
database restructuring
database reorganisation
(b)
- to ensure that it meets with the customer's orginal requirements ( correct any errors from the design phase)
- to incorporate any new changes in requirements
- to optimise retrival performance
112
(a) In a data warehousing system, why is data cleaning (also known as
cleansing or scrubbing) necessary before data from a source can be entered
into the warehouse database?
[2 marks]
(b) Data extracted from a database may be expected to require less cleaning
than data held in a file-based system. Explain why.
[1 mark]
(c) What are the two steps generally necessary to carry out data cleaning?
(a)
to remove any errors or inconsistencies in the data
(b)
because database is managed by scheme and constraints controlling data integrity, whereas file based dataisn't
(c)
data analysis and definition of rules for how it will be cleaned (because of dependencies)
113
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
