1



Your task during the classwork is to design and construct a database and use it to
store and retrieve data. You are advised to read the whole document carefully,
paying particular attention to the Marking Criteria section.

2) Learning Outcomes:
After completing this assignment you will have gained experience of the process
of designing a database system, starting from an informal specification and
formulating database queries using SQL.

3) Task:
Read the scenario below carefully. From the description, produce the following:

a) the logical design for the database.
b) the SQL code to construct the logical design tables.
c) the SQL code to populate the tables.
d) the SQL code to answer the queries listed
e) a critique of your design and implementation.

The database must be built using Oracle.

2

4) Scenario: Industrial units system.

A property management organisation has a number of industrial sites. These
sites are each identified by a site number, have a name and consist of a
number of industrial units. Individual units are given a unit number however a
particular unit number may occur on one or more sites. For each unit, the floor
area (in square metres) and zoning (Light Industry, Heavy Industry, Distribution
Centre or Hazardous Storage) must be recorded.

A full-time site manager, for whom a unique employee number, name, home
address and telephone number are recorded, manages each site. The site
manager supervises a small team of secretarial staff at each site and may
mentor other site managers. A manager may not be mentored. A unique
employee number, name, home address and telephone number are also
recorded for each member of the secretarial staff as well as the date that the
secretary started work for the organisation. Managers and secretarial staff are
employees of the property management organisation. Each manager only
manages one site.

A record is kept of maintenance jobs carried out on units. Most maintenance
jobs are categorised as emergency or routine and are identified by a unique
job number. Each maintenance job has a log date and a description (e.g.
painting, repair windows) and may involve work on one or more units on a site,
although some units have never needed maintenance jobs. For emergency
work the date and time of the emergency are recorded.

An enhanced entity relationship model was developed to represent the database
and included the following entities, attributes and relationships:

a) Entity meaning:
Industrial site. The industrial sites managed by the property organisation
Unit. The units on each industrial site
Employee. The employees of the property organisation
Manager. The managers employed by the property organisation
Secretary. The secretaries employed by the property organisation
JobUnit. A linker entity introduced to represent the M:N relationship
Job. The maintenance jobs carried out on the Units
Emergency. Emergency jobs carried out on the Units
Routine. Routine jobs carried out on the Units

3

b) List of entities and their attributes:
Site(s_number, name)
Unit(uid, unit_number, floor_area, zone)
Employee(employee_number, name, home_address, telephone_number)
Manager(m_number)
Secretary(s_number, start_date)
JobUnit(ju_number)
Job(j_number,description, logdate)
Emergency(e_number, e_date_and_time)
Routine(r_number)


c) Relationships:
Name Entities Degree Optionality
Contains Site, Unit 1:N Obligatory on both
Manages Manager, Site 1:1 Obligatory on both
Supervises Manager,
Secretary
1:N Obligatory on both
Ju1 Unit, JobUnit 1:N Obligatory on JobUnit
Optional on Unit
Ju2 Job, JobUnit 1:N Obligatory on both
Mentors Manager,Manager 1:N Optional on both

d) Assumptions:
Relationships Contains, Manages and Supervises are obligatory on both
participating entity types.


e) EER diagram:





4
5) The logical design for the database.

Table structures must be written down in the following format:

TABLE_NAME(Primary-key-attribute(s), Non-key-attribute1, Non-key-attribute2.....).

Using the enhanced entity relationship model write down a table structure for each
entity taking care that:
• each attribute becomes a column.
• the unique identifier becomes the primary key and is indicated by
underlining
• subtype/supertype entities are represented in one of three methods
described in the lectures

Use Table CW-1 as a guide to the way of representing the relationships between
entities. Write down table structures or modify existing structures to represent
relationships in the system.

1 : 1 1 : N N : M
Obligatory on
neither
New table to
represent
relationship Post
identifiers as
candidate key
New table to
represent
relationship Post
identifiers as
candidate key
New table to
represent
relationship Post
identifiers as
candidate key
Obligatory on
one
Post identifier of
non-obligatory to
obligatory table
New table to
represent
relationship Post
identifiers as
candidate key


-
Obligatory on
many


-
Post identifier of
"one" table to
"many" table
New table to
represent
relationship Post
identifiers as
candidate key
Obligatory on
both
Post all attribute
into one table
Post identifier of
"one" table to
"many" table
New table to
represent
relationship Post
identifiers as
candidate key

Table CW-1: Representing relationships in tables.

Physical database design is not necessary in this exercise and should not be
carried out.
5
6) SQL CREATE Statements.
Write and execute the SQL code to construct the logical design tables. Use
appropriate integrity constraints. The submission of this element must be
accompanied by the feedback received from Oracle when each create statement
is executed.

7) SQL INSERT statements.
Populate each table with a limited set of data, including only enough rows to
demonstrate the features of your design. The submission of this element must be
accompanied by the feedback received from Oracle when each insert statement
is executed.

8) Querying the database
You now need to write some queries on your database. The queries must be
useful and not artificially constructed simply to fulfil the criteria listed. You may
use ANSI or non-ANSI join syntax. Write SQL statements that will:
a) carry out a join between two different tables, restrict the result using a WHERE
clause and use a function and a group by clause.
b) execute a sub-query that has a function in the nested part in the manner
shown in '4.02: Video: Sub-queries'.
c) execute a correlated-query that has a function in the nested part in the manner
shown in '4.03: Video: Correlated Queries'.
d) answer the question: what is the name of each secretary and the name of the
manager who supervises each of these secretaries?
e) answer the question: what are the names of all the managers and the names
of the other managers they mentor (if any)? Note that if a manager does not
mentor any other managers, their name should be listed in the same column of
the resulting table as those managers who do mentor others.

The output of Oracle SQL queries can be captured in a file by typing:
spool outfile

at the SQL prompt. All screen output is then copied to a file with the name
“outfile.lst”. The spooling can be stopped by typing:
spool off

at the SQL prompt.

9) Critique of your database.
A 400 word (+/-10%) critique of your database, highlighting the strengths and
weaknesses of your solution and giving reasons for decisions that you have taken
in the design and implementation. You must also identify the functional
dependencies contained in the tables and explain your table structures in the
context of third normal form.


6
10) Marking Criteria

a) A list of the table structures produced by logical design showing the attributes
and primary keys. (15 %)
b) The SQL create statements (including the specification of integrity constraints)
for creating the tables from (a). (25%)
c) The SQL insert statements for populating the tables with a small sample of
data. The Oracle feedback from the execution of the insert statements must be
included. (15%)
d) The SQL queries (a) – (e) above. For queries (a) - (c), you must first give
the narrative question that is answered by the SQL query. This must be in the
form of a question and not simply a paraphrase the SQL command. For queries
(a)-(e), include the SQL and finally the query output. (20%)
e) Critique. (20%)
f) A further 5% will be awarded for STRICT adherence to the formatting
requirements and page limitation.


11) Formatting requirements and page limitation.

Your submission must be in a single PDF file and have a maximum of six pages
(A4, single column, portrait orientation, 11pt font, no coversheets etc, all black
text on a white background, minimum 2cm margin all round, all pages numbered1)
including only the elements shown under Marking Criteria (a) to (e). The solution
must be typed (not hand written). Items (a) to (e) must be combined into a single
document in the order shown and submitted through the link on the class Myplace
page. The document must not be zipped and must not contain screen shots or
other elements that cannot be processed by Turnitin. If a Turnitin error is returned
when you upload the document (eg "Turnitin has returned an error with your
submission: Your submission must contain 20 words or more"), it means that the
PDF contains text as images. You must OCR (optical character recognition) the
document to produce text and resubmit it so that it can be processed by Turnitin.
SQL queries must be formatted in the style shown in the Introduction to Lecture 6.
Integrity constraints must be declared as part of table CREATE statements and
not created by ALTER TABLE statements.


































































































































































































































































































学霸联盟