COMP S359
Specimen Examination Paper
RELATIONAL DATABASES : Theory and Practice
Time Allowed: 3 hours
关系数据库代写 Instructions: Answer this examination paper in English. Read the instructions in the examination paper carefully and write your answers
Instructions 关系数据库代写
- Answer this examination paper in English.
- Read the instructions in the examination paper carefully and write your answers in theanswer book provided. Answers recorded elsewhere will not be marked. Begin each question on a new page and write the question number at the top of each page you have worked on.
- Write any rough work in the answer book or this examination paper and cross it throughafterwards. Rough work will not be marked.
- Write clearly. It may not be possible to award marks where the writing is very difficult to read.
- After the invigilator has announced that the examination has started, write your examination number, student number and course code on the cover of the answer book or other sheet(s) as distributed by the invigilator. Failure to do so will mean that your work cannot be identified.
- At the end of the examination, hand over the answer book and the examination paper to the invigilator.
- Do NOT open this examination paper until you are told to do so, otherwise you may be disqualified.
Part 1 关系数据库代写
Answer ALL questions in this part.
Each question should be answered on the question paper in the space provided.
Each question in Part 1 is worth 5 marks.
Question 1
(a) Give a brief explanation of the difference between the terms information and data.
(b) Consider the following example form.
Course title: Relational databases: theory and practice
Course code: COMPS359
Course manager: John Sykes
Students enrolled: 618
(i) Give a sentence that represents your understanding of the information content of the form.
(ii) You are told that the value for ‘Students enrolled’ on the form is an example of derived data. Explain what this means in terms of data in a database.
Question 2 关系数据库代写
Consider the diagram in Figure 1, which shows occurrences of the Supplies
relationship connecting occurrences of the entity types Supplier and Part. The figure represents data at a specific instant in time. (It may not be typical for all occurrences of relationships that may occur at different times.)
(a) State the number of occurrences of the Supplies relationship shown in Figure 1.
(b) What can you deduce about the cardinality and participation conditions of the Supplies relationship based on the occurrences given?
(c) From your answer to (b) and the further requirement that a part must be supplied by a single supplier, draw an E–R diagram showing the Supplies relationship between the two entity types Supplier and Part. Your diagram should include cardinality and participation conditions.
Question 3
Figure 2 gives a fragment of an E–R model for an organisation in which some staff members are assigned sole use of a computer, whereas others are not assigned a particular computer but share those in rooms to which they have access.
Assume that the attributes StaffNumber, Name, Code and Type are defined over the domains StaffNumbers, Names, Codes and Types, respectively. Write down a relational representation for this E–R fragment that is consistent with the relational theory taught in Block 2.
Question 4
Figure 3 gives an E–R diagram of staff and the tasks they are associated with, and Figure 4 gives an incomplete relational representation of Figure 3.
Write down relational algebra expressions to satisfy each of the following constraints.
(a) Each staff member is associated with at least one task.
(b) A staff member can be associated with a secretarial task only if his/hergrade is 2 or above.
Question 5 关系数据库代写
The following table depicts a relation Vets.
(a) Demonstrate that Vets has no functional dependencies with determinant PersonName and with the right-hand side consisting of a single attribute.
(b) 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? Justify your answer.
Question 6
The following tables are used in the database of a furniture company to record the purchases of sofas.
sofa
| sofa_design | price |
| milan | 450 |
| roma | 500 |
| venice | 500 |
| turin | 600 |
purchase
| code | customer_name | design |
| 007 | Smith | milan |
| 018 | Jones | roma |
| 216 | Smith | roma |
| 309 | Smith | milan |
| 573 | Jones | turin |
| 663 | Jones | roma |
customer
| name | location |
| Smith | Bedford |
| Jones | York |
| Brown | Bristol |
Give the tables returned by each of the following queries.
(a)
SELECT * FROM sofa WHERE price > 500
(b)
SELECT sofa_design
FROM sofa
WHERE (SELECT COUNT(*)
FROM purchase
WHERE design = sofa_design) < 2
(c)
SELECT location, delivery
FROM (SELECT customer_name, COUNT(*) AS delivery
FROM purchase
GROUP BY customer_name) t1, customer
WHERE customer_name = name
Question 7 关系数据库代写
The following tables are part of a database used to store information about the members of a CD lending library, and the CDs that they currently have on loan. The member table contains the membership number, name and date of birth of each member; NULL is used if the date of birth of the member is not known. Each row of the loan table contains the details of a loan: the membership number of the borrower, the name of the CD, and the date on which the CD is due to be returned.
member
| member_no | name | date_of_birth |
| m17 | Wilson | 1958-06-03 |
| m38 | Young | NULL |
| m41 | Morse | 1964-11-21 |
| m44 | Peters | NULL |
loan
| member_no | cd_name | due_date |
| m17 | Classic Jazz | 2006-11-05 |
| m17 | Smooth Jazz | 2006-11-05 |
| m38 | Rock Anthems | 2006-11-09 |
| m44 | The Hits | 2006-11-07 |
| m44 | Pop Greats | 2006-11-10 |
Write queries to answer the following requests.
(a) List the membership number, name and date of birth of each member for whom a date of birth has been stored.
(b) Give the membership number and name of each member who is currently borrowing one or more CDs.
(c) 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.
Question 8 关系数据库代写
The administrator of the CD library in Question 7 has defined a procedure make_loan. The definition contains the declaration
make_loan(member_no IN CHAR(3), cd_name IN VARCHAR(20))
where CHAR(3) and VARCHAR(20) are the column types of membership numbers and CD names, 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.
(a) Give two reasons why using this procedure may be preferable to working directly with the loan table.
(b) In addition to the make_loan procedure, a function items_borrowed is defined which returns the number of CDs that a member has borrowed. Its signature is
items_borrowed(member_no IN CHAR(3)) RETURN 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 borrowing the two CDs, or fails if that member would then have borrowed more than 3 CDs. You should use the procedure
make_loan and the function items_borrowed to answer this question.
Question 9
An IT company has a small library of books on computing that its employees may borrow. When a particular book is already on loan, employees are able to reserve it. The entity–relationship model for the library is shown in Figure 5.
Revise the entity–relationship model for the library to show how you would record the date when each book was last borrowed [1 point] and the date when each reservation was made [4 points].
Entity–relationship diagram
Entity types
Additional constraints
Assumptions
Limitations
Question 10
(a) Give three reasons why it may be necessary to restructure a database after it has been built and installed.
(b) Give two reasons why denormalisation is not usually performed unless a performance need arises.
Question 11
(a) What do (i) OLAP and (ii) OLTP systems facilitate within an organisation?
(b) Briefly explain what is meant by the term ‘time variant’ for a data warehouse, and describe the main consequence of this in terms of a multi-dimensional model.
(c) Give one reason why it is customary to implement a data warehouse separately from the operational systems that are the sources of its data.
Question 12 关系数据库代写
The following table stores the names and test scores of some students in a class.
student
| name | score |
| Peter | 80 |
| Mary | 83 |
| Amy | 73 |
| David | 54 |
| Paul | 61 |
Given the following PHP code that manipulates the database table.
<?php
try {
$db = new PDO('oci:dbname=//localhost/xe', 'user1', 'pass1'); // (a1)
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
$result = $db->exec("DELETE FROM student WHERE score < 70"); // (a2)
echo "Result: $result <br />";
}catch (PDOException $e) {
echo 'Error: ' . $e->getMessage();
}
?>
(a) In the PHP code, two lines are marked (a1) and (a2). Describe what these lines do to the DBMS.
(b) What is the content of the student table after executing the PHP code? What is the output of executing the PHP code?
(c) Instead of the exec() function, which PDO function shall we use to obtain the result set of executing a SQL SELECT statement?
Part 2 关系数据库代写
Answer TWO questions in this part.
Use a separate answer book for your answers to Part 2 questions. Each
answer should start on a new page.
Each question in Part 2 is worth 20 marks.
Question 13
Figure 6 shows the E–R diagram and corresponding entity types of a manufacturing company.
(a) Give a relational representation which corresponds to the E–R diagram and entity types above, making and stating reasonable assumptions about the domains.
(b) Write a relational constraint that a factory cannot have the same telephone number as a customer.
(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.
(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.
Question 14 关系数据库代写
This question is about a software company’s database which stores information about its staff and the projects on which they work.
Each project has exactly one manager and is located at exactly one site. One or more developers work on any given project. Because many staff work remotely, a manager or a developer can work on projects at different sites. A developer’s task on a project may be design, coding or testing; a developer can work on only one task on any one project, but can work on different tasks on different projects.
The database schema diagram in Figure 7 shows how the database is currently implemented:
The following SQL is used to implement the assignment table in this fragment of the database:
CREATE TABLE assignment (
developer_code staff_codes,
project_name project_names,
location sites,
manager_code staff_codes,
task tasks,
PRIMARY KEY (developer_code, project_name),
CONSTRAINT fk_developer_code
FOREIGN KEY (developer_code) REFERENCES
developer,
CONSTRAINT fk_manager_code
FOREIGN KEY (manager_code) REFERENCES manager
)
The domains staff_codes, project_names, sites and tasks are defined separately in the database. The tables developer and manager, which contain the staff codes of the developers and project managers in the company, are also part of the database. You may assume for the purposes of this question that no constraints are defined on assignment apart from the key declarations in the above definition.
(a)
The following table shows some typical values for the assignment table.
assignment
| developer_code | project_name | location | manager_code | task |
| J736 | mp5 | Slough | R376 | NULL |
| C284 | HiDef-TV | Blackburn | S273 | testing |
| J253 | HiDef-TV | Blackburn | S273 | design |
| J253 | RapidRSS | Blackburn | R376 | coding |
(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).
(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.
(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:
project
| project_name | location | manager_code |
| mp5 | Slough | R376 |
| HiDef-TV | Blackburn | S273 |
| RapidRSS | Blackburn | R376 |
assignment
| developer_code | project_name | task |
| J736 | mp5 | NULL |
| C284 | HiDef-TV | testing |
| J253 | HiDef-TV | design |
| J253 | RapidRSS | coding |
For the remainder of this question, you should assume that the tables project and assignment are owned by a user with authorisation identifier administrator .
(i) 关系数据库代写
As the database schema diagram for the restructured database shows, each project has exactly one manager, and may employ zero to many 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.
(ii)
Why might administrator find it useful to create a role, manager_role, to manage access to manager_view?
(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 developer to a particular task 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.
Question 15 关系数据库代写
An innovative company is planning to introduce information technology into the kitchen by developing an electronic cookery book, a database that will provide easy access to recipes from a series of cookery books. The database is being developed for the domestic market to provide an extensive range of traditional and new recipes plus nutritional information for health-conscious users.
Each cookery book comprises a number of documents each of which is either a recipe or nutritional information about a foodstuff that may be an ingredient of one or more recipes. Each document is identified by a unique number. Each recipe is found in only one cookery book, whereas nutritional information about a particular foodstuff may be found in several books.
A data analyst has developed two different entity–relationship models for the electronic cookery book, which are shown in Figures 9 and 10.
(a) Explain how both entity–relationship models represent the data requirements of the electronic cookery book.
(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.
(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.
更多代写：C语言加拿大代写推荐 gre在家代考 英国经济统计网课代考 理工论文作业代写 英文历史论文代写 essay plan代写
