CS990 Database Fundamentals.
1. Aim Of The Assignment:
Your task during the classwork is to design and construct a database and use it to
store and retrieve data. This is an individual task and must be accomplished
without collaboration or collusion.
2. Task:
Read the following description of a data model. From the specification, produce a
logical design for the database, the SQL code to construct and populate the tables
with a minimal amount of data that is needed to carry out the queries specified. The
database must be built using Oracle.
A database is required to store information concerning a manufacturing company
as follows:
The company maintains a series of factories at which it manufactures products.
Each factory has a unique factory number as well as an address and telephone
number. Each factory has a manager, who can be identified by her/his staff
number. Managers are employees of the manufacturing company and all have a
qualification. The company also employs secretaries, although secretaries are not
assigned to factories. One secretary is designated as the supervisor of the other
secretaries. All secretaries have staff numbers. A record is required of each
employee's name and home telephone number. In addition, for secretaries, the
administrative grade is also recorded. A manager is responsible for only one
factory and each factory has one manager.
Each factory has several vehicles that it uses. Each vehicle has a registration
number and details of the make and model also need to be recorded. Routine
maintenance is carried out by one of the larger factories. Such large factories have
a status of 'regional centre', as opposed to other factories that have a status of
'local centre'. A vehicle is always maintained by the same factory. Some factories
don’t have vehicles assigned to them and some vehicles are not assigned to
factories.
An enhanced entity relationship model was developed to represent the database
and included the following entities, attributes and relationships:
Entity meaning:
Employee. A super-type for the employees of the company.
Manager. A sub-type for the managers of the company.
Secretary. A sub-type for the secretaries.
Factory. A super-type for the factories owned by the company.
Regional. Sub-type for the regional centres.
Local. Sub-type for the local centres.
Vehicle. The vehicles owned by the company.
List of entities and their attributes:
Employee(Esnum, Ename, Ephone)
Manager(Esnum, Qualification)
Secretary(Esnum, Sadmin)
Factory(Fnum, Faddr, Ftel)
Regional(Fnum)
Local(Fnum)
Vehicle(Vreg, Vmake, Vmodel)
Relationship
Relationship name Entities Degree Optionality
Assigned Manager, Factory 1:1 Obligatory on both
Has Factory, Vehicle 1:N Obligatory on
neither
Maintains Regional, Vehicle 1:N Obligatory on both
Supervises Secretary,
Secretary
1:N Obligatory on
neither
Assumptions:
All regional centres maintain at least one vehicle.
All employees are secretaries or managers
EER diagram.
3. Logical design
Table structures should be written down in the following format:
TABLE_NAME(Primary-key-attribute,Non-key-attribute1, Non-key-attribute2.....).
Using the enhanced entity relationship model from Section 4.1, write down a table
structures 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.
4 Creating and loading the database
Use appropriate integrity constraints. Populate each table with a limited set of data
i.e. only enough to show that the queries work.
5 Querying the database
You now need to write some queries on your database. The queries must be useful
queries and not artificially constructed simply to fulfil the criteria listed. All queries
require a WHERE clause of the form. ‘…WHERE ATTRIBUTE = Value…’ to limit
the rows returned (Value can be a text, numeric or date value). Write
SQL statements that will
(i) carry out a join between two tables and use the group by clause.
(ii) execute a sub-query.
(iii) execute a correlated-query.
(iv) carry out a self join that uses primary key/foreign key attributes.
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
6. Submission
Your submission should consist of the following.
i) A list of the table structures produced by logical design showing the attributes
and primary keys. (15 %)
ii) The SQL create statements (including the specification of integrity constraints)
for creating the tables. (25%)
iii) The SQL insert statements for populating the tables with a small sample of data.
(15%)
iv) The SQL queries listed in Section 4.5 together with a narrative explanation of
each query (do not paraphrase the SQL commands) and its output. (25%)
v) A 200 word 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. (20%)
Items (i) to (v) should be combined into a single document and submitted through
the Turnitin link on the class Myplace page. The document must not be zipped. All
work will be evaluated for originality. Submission must be completed by 5.00pm on
Friday 2nd March 2018.
This exercise is worth 25% or the overall marks for this module