Part 2 Flashcards
(a)
Give a relational representation which corresponds to the E–R diagram
and entity types above, making and stating reasonable assumptions about
the domains.
[11]
(a)
model ManufacturingCompany
domains
FactoryCodes = {F001…F999]
Addresses = string
TelephoneNumbers = string of numerals
ProductCodes = {P001…P999}
Descriptions = string
CustomerCodes = {C001…C999}
Names = string
relation Factory
FactoryCode: FactoryCodes
FactoryAddress: Addresses
TelephoneNumber: TelephoneNumbers
primary key FactoryCode
{mandatory participation in Makes}
constraint
((project Factory over FactoryCode)
difference
(project Product over FactoryCode))
is empty
relation Product
ProductCode: ProductCodes
Description: Descriptions
FactoryCode: FactoryCodes
primary key ProductCode
{relationship Makes}
alternate key FactoryCode
foreign key FactoryCode references Factory
Note: An alternative representation of the Makes relationship has the foreign and alternate key in Factory and the constraint in Product.
relation Orders
ProductCode: ProductCodes
CustomerCode: CustomerCodes
primary key (ProductCode,CustomerCode}
foreign key ProductCode references Product
foreign key CustomerCode references Customer
relation Customer
CustomerCode: CustomerCodes
CustomerName: Names
CustomerAddress: Addresses
CustomerTelephoneNumber: TelephoneNumbers
primary key CustomerCode
{mandatory participation in Orders}
constraint
((project Customer over CustomerCode)
difference
(project Orders over CustomerCode))
is empty
(b)
Write a relational constraint that a factory cannot have the same telephone
number as a customer.
(b) constraint ((project Factory over TelephoneNumber) intersection
(project Customer over CustomerTelephoneNumber) rename (CustomerTelephoneNumber as TelephoneNumber) )
is empty
Note: Of course, this depends on TelephoneNumber and CustomerTelephoneNumber being defined over the same domain, so that the two projected relations are union compatible.
(c)
Suppose that the structure of the company changes so that a factory
makes more than one product but each product is made at only one
factory. Give the changes to Figure 6 which result from the change in
structure. Note: You need not redraw the whole E–R diagram or copy
down all the entity types; just give the parts of the diagram and/or the
entity types which have changed.
Discuss briefly how a corresponding relational representation will need to
be adjusted in the light of these changes.
relation Customer
CustomerCode: CustomerCodes
CustomerName: Names
CustomerAddress: Addresses
CustomerTelephoneNumber: TelephoneNumbers
primary key CustomerCode
{mandatory participation in Orders}
constraint
((project Customer over CustomerCode)
difference
(project Orders over CustomerCode))
is empty
(d) Suppose that you wish to implement the relational representation given in your answer to part (a). Write down the SQL statement which creates a table with properties corresponding to the Customer relation, including a constraint which implements the mandatory participation of Customer in Orders. You may assume that the corresponding domains and any other relevant tables such as orders have been implemented.
CREATE TABLE customer (
customer_code customer_codes,
customer_name names NOT NULL,
customer_address addresses NOT NULL,
customer_telephone_number telephone_numbers
NOT NULL,
PRIMARY KEY (customer_code),
CHECK (customer_code IN
(SELECT customer_code FROM orders)))
(i)
Give two disadvantages of storing information about the projects and
locations in a table of this form (assuming that the project names and
locations are not stored elsewhere in the database).
(i) First, the redundancy means that a failure to update the database correctly could lead to inconsistency. For example, the requirement that each project has exactly one manager would be violated if one row states that the project HiDef-TV is managed by staff member S273 and another row states that it is managed by staff member R376.
Second, because assignment describes the allocation of developers to projects as well as the projects themselves, no data can be stored about a project before staff have been allocated to it (developer_code cannot be NULL, as it forms part of the primary key for assignment).
This question assesses your understanding of the problems that may arise from certain database designs. The two examples given here are typical, but other solutions are marked on merit.
(a)(ii)
In order to address some of the problems of the current database
design, the database is to be restructured. The assignment table is
to be restructured into two tables that are in Boyce–Codd normal
form (BCNF).
You are required to carry out this restructuring. You must assume that data already exists in the assignment table, and that this data must not be lost during the restructuring process.
Give a sequence of SQL statements to restructure the table as required. For each statement, you should also give a brief description of what the SQL does, to show how the restructuring is carried out in SQL.
Your solution should not attempt to alter the developer or manager tables.
(ii) The first stage is to define the new project table:
CREATE TABLE project (
project_name project_names,
location sites,
manager_code staff_codes,
PRIMARY KEY (project_name),
CONSTRAINT fk_manager_code_2
FOREIGN KEY (manager_code) REFERENCES manager)
Note that most of this definition is simply part of the original definition of assignment.
The next stage is to copy the existing data from the assignment table into the appropriate rows from the project table, ensuring that no rows are duplicated:
INSERT INTO project (project_name, location, manager_code) SELECT DISTINCT project_name, location, manager_code FROM assignment
Any existing constraint using the columns you intend to drop will need to be removed.
ALTER TABLE assignment
DROP fk_manager_code
Next, the redundant columns should be dropped from the assignment table:
ALTER TABLE assignment
DROP location
ALTER TABLE assignment
DROP manager_code
Finally, a foreign key is required from assignment to project: ALTER TABLE assignment ADD CONSTRAINT fk_project FOREIGN KEY (project_name) REFERENCES project
This question assesses your understanding of how SQL can be used to restructure a database. While there are several steps, the SQL in each step is straightforward. When presented with a question like this, you should always indicate how the steps in your solution lead to the final result, as you may then receive credit for later stages despite mistakes in the intermediate stages. As before, minor syntactic errors in the SQL will be overlooked.
(b) This part of the question assumes that the normalisation described in part (a)(ii) has been carried out correctly, and so the database contains tables assignment and project. The table described in part (a)(i) therefore has been normalised into:
(i)
As the database schema diagram for the restructured database
shows, each project has exactly one manager, and employs one or
more different developers, each of whom may be employed on
different tasks.
You are asked to create a view that will allow each manager to see
which developers are working on the projects that (s)he manages.
The view should also state which project each of the developers is
working on, and the tasks that those developers have been assigned
for that project.
Give the SQL that administrator would use to define a view called
manager_view which has columns named project, developer
and task. If manager_view is viewed by a member of staff who is
a manager, then for each of that manager’s projects, the view should
contain the project name, the staff code of the developer, and the
task that the developer is employed on.
You should assume that for developers and managers, their
authorisation identifier is the same as their staff code. You may wish
to use the USER variable provided by SQL, which returns the
authorisation identifier of the user executing a statement.
[4]
(ii)
Why might administrator find it useful to create a role,
manager_role, to manage access to manager_view?
[2]
(iii)
The following behaviours are required for the database.
1
Only the user with authorisation identifier personnel can add new
projects to, or remove projects from, the database. This user
should also be able to see all information about existing projects.
2
Managers should be able to see which developers are working
on their projects, and to which tasks they have been assigned.
Managers should not be able to see this information for projects
which they do not manage.
3
Only the manager of a project may allocate a task to a particular
developer on that project.
Briefly describe the privileges that administrator should grant to
personnel and manager_role to fulfil these requirements. You
should only consider privileges granted on the tables project and
assignment and the view manager_view.
(b) (i)
CREATE VIEW manager_view AS (
SELECT p.project_name AS project,
developer_code AS developer, task FROM project p, assignment a WHERE p.project_name=a.project_name
AND manager_code=USER )
Your solution should show the correct syntax for defining a view, the correct use of USER to personalise the view, and an appropriate query in the view body. Note that the columns must be renamed for the full marks; you could also use the alternative syntax:
CREATE VIEW manager_view(project, developer,
task) AS ( SELECT p.project_name, developer_code, task
FROM project p, assignment a WHERE p.project_name=a.project_name AND manager_code=USER )
(ii) By defining a role (manager_role) to which all managers belong, administrator can grant or revoke privileges to all managers at once. Also, when a new manager is added to the database, the new manager can be granted all the privileges granted to other managers, simply by being added to manager_role.
If a role were not used, administrator would need to grant or revoke each privilege for each manager individually. This would be both tedious and error-prone.
When asked to justify a solution, it is advisable to give a benefit that arises from using the solution as well as a disadvantage of not using the solution.
(iii) To fulfil requirement 1, administrator would grant select, insert and delete privileges on project to personnel.
Although the question does not explicitly state that personnel should be able to update the base table project, it would not be unreasonable for personnel to be granted update privileges. Therefore, granting all privileges to personnel would not be marked down.
To fulfil requirement 2, administrator would grant select privilege on manager_view to manager_role.
To fulfil requirement 3, administrator would grant update privilege on the column task in manager_view to manager_role.
For requirements 2 and 3, only privileges on the view should be granted to avoid a manager viewing or altering rows relating to projects which are not his own.
No other privileges need to be granted to fulfil the stated requirements.
It is important to state that no other privileges are required, to indicate that you understand that granting privileges on the procedure and views is adequate for the requirements. A common mistake is to assume that access privileges must be granted on the base tables in order to access them via a view.
Although you are not specifically asked for the SQL, you may wish to provide it if you feel that it would clarify your answer. The SQL that administrator would have to execute is:
GRANT INSERT ON project TO personnel GRANT DELETE ON project TO personnel GRANT SELECT ON manager_view TO manager_role GRANT UPDATE (task) ON manager_view TO
manager_role
(a)
Explain how both entity–relationship models represent the data requirements of the electronic cookery book. [5]
(b)
As the entity subtype concept has no direct counterpart in relational theory, in Block 4 we said that in the database design task we usually choose
• either to represent all subtypes by a single table
• or to use a separate table for each subtype.
For each of these approaches, draw a database schema diagram for the electronic cookery book shown in Figure 10. For each database schema diagram, list the foreign key columns and the tables they reference. [10]
(c) Describe the problems that you might encounter when populating a database with data. Outline approaches that you could use to overcome these problems whilst maintaining the integrity of the data.
(a) Both models have entity types that record (the same) data about cookery books, recipes and foodstuffs. The requirement that ‘Each recipe is found in only one cookery book whereas nutritional information about a particular foodstuff may be found in several books’ is represented explicitly in the model shown in Figure 7 by the degrees of the Includes and Comprises relationships. In the model shown in Figure 8 it has to be represented by the additional constraint c.1.
The requirement that ‘Each document is identified by a unique number’ is represented explicitly in the model shown in Figure 8 by making Recipe and Foodstuff subtypes of the Document entity type. In the model shown in Figure 7 it has to be represented by the additional constraint c.1.
(c) Constraints that enforce the integrity of the data are checked at the end of execution of every SQL statement, even though those SQL statements may form part of an SQL transaction. There will be many situations when this default will prevent rows being inserted into database tables, notably the constraints required to enforce mandatory participation of both ends of a relationship.
Standard SQL allows us to defer constraint checking until an SQL transaction commits. However, not all DBMSs, including SQL Anywhere, support deferred constraint checking fully. In such situations we have to employ one of the following approaches.
1 Drop the minimum number of constraints to allow data to be inserted into the tables, check the integrity of the data, then reinstate those constraints that were dropped.
2 Drop some (as in 1) or all of the constraints, then use SQL procedures as the sole means of updating the database, and to validate the constraints on the data. Each procedure requires an execute privilege, which enables users to update the database tables without having the privilege on the tables required to use an SQL statement to update the tables directly.
M359 Specimen
(a) Give a relational representation of the E-R model fragment above. Do not
add anything that is not explicitly represented in the fragment. Include
comments to relate the parts of your relational representation to the
corresponding aspects of the E-R model fragment.
domains
PropertyNos = Integer
Addresses = {string of alphabetic characters}
AvailabilityDates = standard dates
ContractNos = Integer
SignedDates = standard dates
StartDates = standard dates
CustomerNos = Integer
Names = {string of alphabetic characters}
RegistrationDates = standard dates
Types = (‘OneStar’, ‘TwoStar’, ‘ThreeStar’) {Constraint c.2}
relation Property
PropertyNo: PropertyNos,
Address: Addresses
AvailabiltyDate: Dates
primary Key PropertyNo
{constraint c.1 AvailabilityDate <= SigningDate}
constraint (select property join contract where availability date >= signedDate) is empty
relation Contract
ContractNo: ContractNos
SignedDate: Dates
StartDate: Dates
PropertyNo: PropertyNos
primary key ContractNo
{relationship IsInvolvedIn is 1:1}
alternate key PropertyNo
{mandatory participation of Contract in IsInvolvedIn relationship}
foreign Key PropertyNo references Property
constraint (project Contract over ContractNo) difference (project SignedBy over ContractNo) is empty
relation Customer
CustomerNo: CustomerNos
Name: Names
RegistrationDate: Dates
Address: Addresses
primary key CustomerNo
relation MaintenanceType
Type: Types
Description: string
primary key Type
{Relationship IsSubjectTo}
relation IsSubjectTo
Type: Types
PropertyNo: PropertyNos
primary key (PropertyNo)
foreign key Type references MaintenanceType
foreign key PropertyNo references Property
{relationship SignedBy}
relation SignedBy
ContractNo: ContractNos
CustomerNo: CustomerNos
primary key (ContrcatNo, CustomerNo)
foreign key ContractNo references Contract
foreign key CustomerNos references Customer
{constraint c.3}
constraint ((project IsSubjectTo over PropertyNo) difference (project Contract over PropertyNo) is empty.
Notes: On domains it would be possible to substitute ContractNos = {C001…C999} etc however this would be a constraint and is not mentioned in the E-R model so have kept it simple.
Difficulties – allowing sufficent space when writing out by hand for FKs and Constraints.
Advice from tutor was that it is acceptable to put constraints at the end and not necessarily in a particular relation unless it obviouly belongs there.
Comments – the question asked for these.
model DogClub
domains
OwnerIds = O001..O999
DogIds = D001..D999
Addresses = string
OwnerNames = string
DogNames = string
BreedNames = string
DoB = Date
Genders = {male, female}
relation Owner
OwnerId: OwnerIds
Name: OwnerNames
Address: Addresses
primary key OwnerId
constraint
((project Owner over OwnerId) difference (project Owns over OwnerId))
is empty
relation Dog
DogId: DogIds
DogName: DogNames
BreedName: BreedNames
DateOfBirth: DoB
Gender: Genders
primary key DogId
constraint
((project Dog over DogId) difference (project Owns over DogId) )
is empty
relation Owns
DogId: DogIds
OwnerId: OwnerIds
primary key DogId,OwnerId
foreign key DogId references Dog
foreign key OwnerId references Owner
(a) Write down the part of the Entity-Relationship model of Dog Club that is
represented by the relational representation in Figure 3. Your model
should consist of two entity types and an Entity-Relationship diagram.
Owner (crow’s foot mandatory) ———– (crow’s foot mandatory) Dog
model DogClub
domains
OwnerIds = O001..O999
DogIds = D001..D999
Addresses = string
OwnerNames = string
DogNames = string
BreedNames = string
DoB = Date
Genders = {male, female}
relation Owner
OwnerId: OwnerIds
Name: OwnerNames
Address: Addresses
primary key OwnerId
constraint
((project Owner over OwnerId) difference (project Owns over OwnerId))
is empty
relation Dog
DogId: DogIds
DogName: DogNames
BreedName: BreedNames
DateOfBirth: DoB
Gender: Genders
primary key DogId
constraint
((project Dog over DogId) difference (project Owns over DogId) )
is empty
relation Owns
DogId: DogIds
OwnerId: OwnerIds
primary key DogId,OwnerId
foreign key DogId references Dog
foreign key OwnerId references Owner
(b) The E-R model is to be extended to include the breed of each dog and its
father (sire) and mother (dam) if they are known, as shown in Figure 4
below.
relation Dog
add
foreign key BreedName references Breed
relation Breed
BreedName: BreedNames
AverageAdultWeight: AverageAdultWeights
CareNotes: string
primary key BreedName
relation IsDam
DogId: DogIds
OffspringId: DogIds
primary key OffspringId
foreign key DogId references Dog
foreign key OffspringId references Dog
relation IsSire
DogId: DogIds
OffspringId: DogIds
primary key OffspringId
foreign key DogId references Dog
foreign key OffspringId references Dog
The following constraints are required so that meaningful data is
represented by the IsDam and IsSire relationships.
Constraint
c.1. A dam must be female.
c.2 A sire must be male.
c.3. A dog can’t be its own dam nor its own sire.
Write down relational constraint expressions for constraints c.1 and c.3
that will enforce these constraints for the relational representation you
gave in your answer to part (b).
c)
c1.
constraint (
select (IsDam join Dog) where Gender = ‘male’
) is empty
c2.
constraint (
select (IsSire join Dog) where Gender = ‘female’
) is empty
c3.
constraint (
(select (IsSire join Dog) where DogId = OffspringId) union (select (IsDam join Dog) where DogId = OffspringId)
) is empty
Consider the relation Dog given at the start of the question (but ignoring
the declared constraint). A database developer has implemented this
relation using the following SQL (you should assume the required
domains have been created and are consistent with those in the DogClub
relational representation).
CREATE TABLE Dog(
DogId DogIds,
DogName DogNames,
BreedName BreedNames,
DateOfBirth DateOfBirth,
Gender Genders,
PRIMARY KEY (DogId) );
(i) In what way(s) might the relational values and the SQL data
recorded by these representations differ? [2]
(ii) Explain why the result of evaluating the relational expression
(project Dog over DogName)
and the SQL statement
SELECT DogName
FROM Dog
might differ even though the relation Dog and the table Dog hold
consistent values representing the same dogs.
[2]
(iii) Amend the CREATE TABLE statement so that the information
recorded is consistent in the two representations, and amend the
project expression or the SELECT statement so that the result of
their evaluation is the same. Explain your amendment in each case.
(i) could have NULL values in the sql data for DogName, BreedName, DateOfBirth, Gender whereas null values aren’t pos in relational model (each cell must have a value)
(ii) there wouldn’t be any duplicates in “project dog…” as all rows in a relation must be unique, whereas “select dogname…” may produce duplicate DogName’s
(iii) change the sql query query to be “SELECT DISTINCT FROM Dog” as the DISTINCT keyword will ensure that only unique rows are returned;
and add NOT NULL to each field definition, ie:
DogNamne DogNames NOT NULL,
BreedName BreedNames NOT NULL,
(a) We have significant choices in how we transform an entity–relationship
model into a first-cut design for a database.
For each of the following areas where design choices can arise (1) outline
the options available, and (2) the criteria used to select an appropriate
option.
(i) relationships
(ii) complex data
(iii) general constraints
[14]
(b) We have significant choices in how we distribute data in a table amongst
the different locations of a distributed database system.
For each of the following where choices can arise (1) outline the options
available, and (2) the criteria used to select an appropriate option.
(i) fragmentation
(ii) replication
a) i) relationships
Options avaliable
Foreign key approach (posted and pre posted)
relation for relations
Criteria of appropiate option
m:n, optional :1 and optional :2 would yeild a relation for relations approach
m:n would be reduced to 1:n and 1:1
mandatory :1 and :n would result in a foreign key approach (FK NOT NULL!)
Additional choice comes in if FK may be NULL (optional condition :1 and :n)
then you could still use the foreign key approach or chose to use the relation for relations approach
b) Complex data
Options avaliable
single column
several column (1 table)
new table
Criteria of appropiate option
What is the purpose of the data? Is it for labelling (read only/no additonal value) or as you concerned over the detail (data turns into meaningful information) of the recorded data (analysis/manipulation)
c) General constraints
Options avaliable
Check
Trigger
Criteria of appropiate option
Check = referencial and domain constraint
Implementation options: insert/update/delete
Trigger = constrain data + relationships
Implementation options: insert/update/delete, before/after, old/new, row/statement
b) not answered that yet
(a) Both tables staff and student are owned by Fred, a user of the
database whose user ID is fred. Fred grants all privileges on the two
tables to Ben and Sara, who are also both users of the database. The
database management system allows simultaneous access to the
database by multiple users.
Ben and Sara are both users of the database, and have all privileges on
both tables staff and student.
M359/C 19 [TURN OVER]
The member of staff named Smith is due to retire from the University, and
will be replaced by another member of staff named Taylor. Taylor will then
teach all the students who are currently taught by Smith. Ben updates the
database with the following statements:
INSERT INTO fred.staff
VALUES (‘t5’, ‘taylor’, ‘tutor’)
UPDATE fred.student
SET tutor=’t5’
WHERE tutor=’t1’
DELETE FROM fred.staff
WHERE staff_number=’t1’
Meanwhile, Sara intends to change the category of all members of staff
who are tutors to authors, if they do not currently tutor any students. She
updates the database with the following statement:
UPDATE fred.staff
SET category=’author’
WHERE category=’tutor’
AND NOT EXISTS (SELECT *
FROM fred.student
WHERE tutor = staff_number)
(i) What problems may arise if Ben and Sara attempt to execute their
code at the same time? [3]
(ii) What mechanisms exist in the DBMS to help prevent the problems
that you identified in part (a)(i) from arising? What are the features
in Standard SQL that Ben and Sara would use to control these
mechanisms? [4]
In your answers to part (a), you should make specific reference to the
particular example given, rather than making general statements about SQL
functionality.
a) i) If Ben’s 1st query runs and then prior to Ben’s 2nd query running then Sara’s query runs, this would result in the new tutor record (t5) having their category changed to author; and then when Ben’s 2nd query ran (updating the student records previously tutored by t1 to t5) then this would result in the staff record t5 having the value ‘author’ in the category column and yet also being referenced as a tutor in the student table….inconsistent data.
a) ii) Transaction management, COMMIT & ROLLBACK statements, Serialized execution
(a) Assuming the staff and student tables contain the data shown in
Figure 4, explain why each of the following SQL statements fails to update
the database.
(i) INSERT INTO staff VALUES (‘t5’, ‘Smith’, ‘technician’)
(ii) DELETE FROM staff WHERE staff_number = ‘t3’
(iii) INSERT INTO student VALUES (‘s7’, ‘Jones’, ‘t5’)
(3 marks)
(b) For the database to satisfy all of the requirements of the training college, the
SQL definition given in Figure 3 needs to be revised in order to implement
the following additional requirements. For each additional requirement, give
the ALTER TABLE statement(s) required to revise the definitions given in
Figure 3 so that the requirement is correctly implemented.
(i) Only staff categorised as tutors can tutor students, and
(3 marks)
(ii) When a row is deleted in the staff table, then the corresponding entry
in the student table (tutor column) should be set to NULL.
(3 marks)
(c) Consider the following query:
SELECT staff_number
FROM staff
WHERE EXISTS (SELECT *
FROM student
WHERE staff.name = name)
(i) Describe the logical processing of this query according to the model given in
the module text.
(4 marks)
(ii) Give an English request that the query answers.
(1 mark)
(d) Give a brief description of the similarities and differences between
(i) SQL functions and SQL procedures
(3 marks)
(ii) SQL triggers and CHECK constraints
a)
i)
domain ‘categories’ for column ‘category’ can only take values ‘tutor’ and ‘author’
ii)
tutor in student references staff and cannot be NULL, deleting this row would cause that.
iii)
tutor references staff and there is no staff_number ‘t5’
b)
i)
alter table student
add constraint constraint only_tutors_teach
check (tutor in (slect staff_number form staff where category = ‘tutor’))
ii)
alter table student
drop constraint tutored_by
add constraint tutored_by
foreign key (tutor) references staff
on delete set null
(c) i) The logical processing model of this query is as follows:
1. Create an intermediate table with all the rows from staff
2. For each row in the intermediate table, process the inner subquery as follows:
2a Create an intermediate table with all the rows from Student.
2b Copy to a new intermediate table all the rows there the student name matches the staff member’s name (As per step 2 above.)
3. Copy to a new intermediate table all the rows (from the table created in step 2) where the intermediate table created in step 2b has a non-zero number of rows.
4. Copy the ‘staff_number’ column from the intermediate table created in step 3, this is the final output.
(c)(ii) I had: List the staff numbers where the staff member’s name is the same as that of a student.
(i)
Both SQL functions and SQL procedures use compound statements to return a result.
SQL functions can return only one value, while SQL procedures can return many results, it does not return a value, but can be used to put values into one or more variables.
(ii)
SQL Triggers and Check constraints provide ways of ensuring data integrity.
A trigger is used for dynamic constraints, whereas a check constraint if used for static constraints i.e. those declared in the table creation. Static constraints cannot constrain how the database change.
(a) Give an E–R diagram, entity types and constraints that correspond to this
relational representation.
(14 marks)
(b) Write relational expressions to express the following constraints:
(i) Each staff member is an author of a module, the chair of a (possibly
different) module, or both.
(3 marks)
(ii) For each module, the publication date of the module cannot be before
the publication date of each of the units comprising the module.
Module (ModuleCode, ModuleName, ModulePublicationDate)
Unit (ModuleCode, ModuleNumber, UnitPublicationDate)
Staff (StaffNumber, Name, Email, DateAppointed)
Draft (ModuleCode, UnitNumber, DraftNumber, DateDue)
c1. Unit is a weak entity type dependent on the entity type Module. So the value of the ModuleCode attribute in an instance of the entity type Unit must be the same value as the ModuleCode attribute of the instance of the Module entity type to which the Unit entity type is related by the relationship IsPartOf.
c2. Draft is a weak entity type dependent on the entity type Unit. So the value of the ModuleCode and UnitNumber attribute pair in an instance of the entity type Draft must be the same value as the ModuleCode and UnitNumber attributes of the instance of the Unit entity type to which the Draft entity type is related by the relationship Has.
b) i)
project staff over staffnumber difference (
project (rename (module) (chair as staffnumber) over staffnumber)
union
project (rename (module join unit) (author as staffnumber)) over staffnumber)
) is empty
ii) (select (unit join module) where unitpublicationdate > modulepublicationdate) is empty
(a) Give an E–R diagram and entity type definitions that correspond to this
relational representation. Your E-R model should comprise just three entity
types, namely, Teacher, Class and Room.
(a)
(i) Draw a database schema diagram for a database design that
represents the data requirements specified by the E-R model for the
operational database system.
[7 marks]
(ii) Briefly describe how each relationship is represented in your database
design.
[3 marks]
(b) The music store has decided to build a data warehouse using ROLAP
technology to facilitate the analysis of the sales of CD tracks in order to
determine the popularity of individual tracks and recording artists.
(i) Describe what each row of the fact table will record and what the
dimension tables are.
[2 marks]
(ii) Draw a star schema for a data warehouse database that would support
the analysis of sales of CD tracks.
a)i)
Database schema diagram
(see attached for diagram)
cd(catalogue_no, title, artist, record_label, date_released)
track( catalogue_no, track_no, title, price)
order ( order_no, date, customer_no)
customer (customer_no, password, name, address, telephone_no, email)
purchased_by(catalogue_no, order_no)
ii)
Relationship Comprises is represented by the foreign key catalogue_no at the n:end of the relationship in track, which references the primary key of the cd table.
Relationship PlacedBy is represented by the foreign key customer_no at the n:end of the relationship in order, which references the primary key of the customer table.
Relationship PurchasedBy is represented by the relation for relationship approach by decomoposing the m:n relationship into two 1:n relationships and an intersection table called purchased_by.
b)
i)
We are told in the question that we are ‘analysing sales’ of CD tracks, so sales will be the fact table.
There will be a need for a dimension time as it is an essential dimension for historical data, which allows analysts to summarise data over different time periods.
We are told there is a need to determine popularity of individual tracks and recording artists so the Track relation for TrackNo, and CD relation for Artist will be dimension tables.
Both the CD and Track relation both have within their primary keys Catalogue_No. In the OU material they all seem to have single identifiers, so it may be necessary to create a unique code for each. I still need clarification from my tutor on how to handle these types of questions.
ii)
(see attached for star diagram).
sales (time_code, catalogue_no, track_no)
time (time_code, data, day, week, month, quarter, year)
cd (catalogue_no, title, artist, record_label, date_released)
track (catalogue_no, track_no, title, price)
(a) Give a relational representation of the E-R model fragment above. Do not
add anything that is not explicitly represented in the fragment. You may
want to include comments to relate the parts of your relational
representation to the corresponding aspects of the E-R model fragment.
(b) Suppose the constraint c.2 is changed so that it becomes
c.2 (changed) Each academic has EITHER the role of an editor OR the
role of an author, but not both.
This changed constraint can be represented in an E-R diagram by the use
of subtypes.
Redraw the E-R diagram above to make use of subtypes, and show the
revisions needed to the Entity types to be consistent with your diagram.
(i) Using the appropriate SQL, explain how this constraint could be
implemented with a domain constraint rather than a table constraint.
When might it be preferable to use a domain constraint instead of a
table constraint? [4]
(ii) Write an ALTER TABLE statement to specify a check constraint to
ensure that the total amount currently being claimed by any member
of staff does not exceed their expense limit, if they have one.
The check constraint is to be given the name check_limit.
a) i)
DECLARE DOMAIN expense_limit INTEGER
CHECK VALUE BETWEEN 0 AND 5000
a) ii)
ALTER TABLE Claim ADD CONSTRAINT check_limit
CHECK (( SELECT SUM(c.amount) FROM claim c WHERE c.staff_id = staff_id)
<= ( SELECT s.expense_limit FROM staff s WHERE s.staff_id = staff_id))
(a) Give an E-R model corresponding to the relational model above with exactly
three entity types, Person, Team and Match. Your model should include an
E-R diagram, entity type definitions and any additional constraint(s).
Person(Name, DateOfBrth, Role)
Team (Name, Ground, League, Manager, ManagerDateOfBirth)
Match(HomeTeam, AwayTeam, Date, Result)
additional constraint
c1. For each match played on a date, there must be a unique Away team
(b) Give constraint declarations of the form
constraint constraint-name (relational-expression) is empty
to represent the following constraints:
(i) In any match, the home team must be different from the away team
(ii) In any match, the home team and the away team must be from the
same league
[6 marks]
(c) What is a surrogate key in SQL? For each SQL table Person, Team,
TeamConsists, Match, resulting from the transformation of the sports
relational model into an SQL database definition, state whether including a
surrogate key would be something to consider or not in the database design.
b)ii) was:
CONSTRAINT (SELECT
((PROJECT (Team rename (League as HomeLeague, Name as HomeTeam)) over HomeTeam, Home League
join PROJECT Match over HomeTeam, Date, AwayTeam)
join (PROJECT (Team rename (League as AwayLeague, Name as AwayTeam)) over
c)
a surrogate key (SK) is a single column primary key (PK), used to replace a composite PK.
Person, yes consider a SK
Team, no, already a single column PK
TeamConsists, yes consider a SK
Match, yes consider a SK
AwayLeague, AwayTeam))
WHERE HomeLeague != AwayLeague) IS EMPTY
For each of the following statements describe and explain the effect of the
statement and the trigger on the salary and tracker tables.
(i)
INSERT INTO salary VALUES (‘frederick’, 20000, 1000)
[2 marks]
(ii)
UPDATE salary
SET salary = 40000
WHERE employee_id = ‘freddie’
[2 marks]
(iii)
UPDATE salary
SET bonus = 4000
WHERE employee_id = ‘freda’
[2 marks]
(iv)
UPDATE salary
SET bonus = 2000
WHERE employee_id = ‘fred’
c)
i)
The act of executing this statement will change Salary to:
employee_id salary bonus
fred 20000 2000
Freda 30000 null
Freddie 30000 2000
Frederick 20000 1000
This statement does not invoke the Trigger as it is an INSERT not an UPDATE.
There is no change to tracker
ii)
This statement does not invoke the Trigger as it is an UPDATE to the SALARY column not the BONUS column this is covered by the trigger.
The act of executing this statement will change Salary to:
employee_id salary bonus
fred 20000 2000
Freda 30000 null
Freddie 40000 2000
Frederick 20000 1000
There is no change to tracker
(a) Give a relational representation of the E-R model fragment above that is
consistent with the theory of Block 2.
relational model Banking System
Domains
BankNames : Alphanumeric
Addresses : Alphanumeric
BranchCodes : Alphanumeric
Sizes : { small, medium, large }
CustomerCodes : Alphanumeric
CustomerNames: Alpha
TrueFalse : {true, false}
relation Branch
BranchAddress: BranchAddresses
BranchCode: BranchCodes
Size : Sizes
BankName: BankNames
Primary key BranchAddress
Alternate key BranchCode
{ represents mandatory participation in HasBranches relationship }
Foreign key BankName references Bank
relation Bank
BankName: BankNames
HeadOfficeAddress: Addresses
Internet : TrueFalse
Primary key BankNames
relation Customer
CustomerCode : CustomerCodes
CustomerName: CustomerNames
Address : Addresses
Primary key CustomerCode
{ constraint c.4 }
Constraint ( (project IsACustomerOf Over CustomerCode) UNION (project HasInternetCustomer over CustomerCode)) Difference (project customer over CustomerCode) IS EMPTY
{ weak entity type representing relation for relationship relationship of IsACustomerOf }
relation IsACustomerOf
BranchAddress: Addresses
CustomerCode: CustomerCodes
Primary key (BranchAddress, CustomerCode)
Foreign key BranchAddress references Branch
Foreign key CustomerCode references Customer
relation HasInternetCustomer
BankName: BankNames
CustomerCode: CustomerCodes
Primary key CustomerCode
Foreign key BankName references Bank
Foreign key CustomerCode references Customer
{ constraint c.5 }
Constraint ( (project HasInternetCustomer over BankName)
DIFFERENCE
(project (select Bank where Internet = ‘True’) over BankName) ) IS EMPTY
a) Following a recent security review, Whizzcom has decided that it is no longer
acceptable to allow all its staff full access to the database. For example, all
staff members should be able to see the current list of projects, but only
managers should be able to alter the assignment of engineers to projects.
Similarly, employees should be able to see only their own personal data.
Briefly describe the facilities that SQL provides to allow the database
administrator to manage complex access control privileges effectively for
large numbers of users. In particular, you should consider how different
users, or groups of users with similar requirements, can be allowed to
access the tables (or parts of tables) in the database.
You should provide simple illustrative examples for your answer.
[10 marks]
(b) The following fragment of a schema diagram shows part of the structure of
the database for the above scenario:
works_on(many, mand)—(sing,mand)project
works_on (employee, project_code)
project (code, staff_required)
It shows the tables and the relationships between the tables and the table
definitions showing primary (underlined) and foreign keys (italics). The fragment
shows only the project and works_on tables.
Engineer and manager are entity subtypes of the staff entity supertype. The
database design could represent the engineers and managers either as a single
table, or as two tables.
Complete the schema diagram for each possible implementation (a single table
for staff or two tables representing engineers and managers separately). You
should also describe any additional constraints required to ensure the
implementation accurately reflects the subtypes.
Your answer should include two completed schema diagrams showing tables and
table relationships. Beneath the diagram you should write down the table
definitions and identify the primary keys (underlined) and foreign keys (circled).
You do not need to write any SQL for this part of the question.
(a)
One of the facilities provided by SQL to limit access for users is to implement views. The views can be designed to present only data a certain user is entitled to use and/or modify. For example, a view can be used to allow a employees to see their own personal data. The employee may determine that data base on the employee identifier.
Another facility of SQL is to grant certain privileges to certain users. If many users need the same permissions, they can be allocated to a certain group (role), and privileges allocated to that group. Each member of the group will benefit of those privileges. For example, the SELECT privilege on table ‘project’ can be given to PUBLIC, which means that all staff will be able to see the data in that table. Managers can be allocated to a group, that will have the SELECT and UPDATE privileges on table ‘works_on’, which means they will be able to view the table and alter the assignment of engineers to projects.
(b)
Single table option:
staff——————-(many, mandatory)[works_on]
staff(staff_id, position, grade, manager)
works_on(staff_id, project_code)
C1 - The position of the staff who works on a project must be ‘engineer’
Two tables option:
manager——(many, mandatory)engineer——(many, mandatory)[works_on]
manager(m_staff_id, grade)
engineer(e_staff_id, grade, manager)
works_on(e_staff_id, project_code)
C1 - No manager and engineer can have the same staff number.
(Not sure whether we should mention that the SQL implementation will need to have constraints to ensure mandatory participation of manager in relationship with engineer, and of engineer in relationship with works_on)
(a) Suppose we have implemented the SQL database definition given in
Figure 2 for the database schema diagram shown in Figure 1. Explain why
attempting to populate the hospital and nurse tables by simply using
SQL INSERT statements will fail without adding any rows to either table.
[5 marks]
(b) As standard SQL allows us to defer the checking of the constraints to the
end of an SQL transaction, we can populate the hospital and nurse
tables using SQL INSERT statements within a transaction. However, SQL
Anywhere does NOT fully support deferred constraint checking but does
allow deferred checking of foreign key (referential) integrity. Briefly describe
how you could populate the hospital and nurse tables using SQL
Anywhere.
[5 marks]
(c) Suppose the hospital trust uses a distributed database system where each
hospital has its own computer, and the nurse table is horizontally distributed
by hospital.
(i) In order to meet staffing needs, a nurse can be transferred between
hospitals. Explain why this transaction requires a more complex
mechanism (than in a single, local database) to ensure consistency,
and briefly describe how it can be managed.
[6 marks]
(ii) With reference to your answer in part (i), comment briefly on how
transaction management might influence performance in a distributed
database system.
(a)
Trying to insert rows in ‘hospital’ table will fail, due to the constraint ‘hospital_in_staffed_by’. Since table nurse is empty, the check constraint will evaluate to false (there are no hospital numbers recorded in ‘nurse’).
Trying to insert rows in ‘nurse’ table will fail too, due to the constraint ‘relationship_staffed_by’. Referential integrity is broken when rows are attempted to be inserted in table nurse. Each row has hospital_no as a foreign key, but there are no hospital numbers recorded in hospital (table is empty). Also, we cannot have hospital_no NULL as it is declared NOT NULL (participation is mandatory).
(b)
We will need to make use of the deferred checking of the foreign key and populate table ‘nurse’ first will all the necessary data (including values for hospital_no). After this we can populate table ‘hospital’. Once both are populated the checking of foreign keys can be done.
(c)
(i)
If it were just one table, then all we would need to do is update the value of ‘hospital’ in the row with the said nurse.
Since it is a distributed database system, it means that a row from the database of the hospital where the nurse used to work needs to be deleted and a row need to be inserted into the database of the hospital where she transferred to. To ensure consistency, we need to make sure that both actions (the deletion and the insertion) were successful. Transaction management can be used here. In a transaction, both locations are asked to prepare to perform the respective operations. Only if the locations give and affirmative answer, the request to commit the changes is sent.
(ii)
Transaction management involves communication over the network, which is usually a slower process than operations on a certain server. Also, communication over a network may fail (hardware or software failure), thus the transaction may not be performed at all. Thus, performance when transaction management is involved may decrease.
