Course: COMP1302 Coursework Number: Contribution:
Data Base Design & Implementation 1 of 1 30% of course
Course Coordinator: Header Sheet ID: Date Set:
Dr. Al-Zobaidie July 2007
This coursework should take an average student who is up-to-date with tutorial work
approximately 30 hours
Plagiarism is presenting somebody else’s work as your own. It includes:
copying information directly from the Web or books without referencing the
material; submitting joint coursework as an individual effort; copying another
student’s coursework; stealing or buying coursework from someone else and
submitting it as your own work. Suspected plagiarism will be investigated and
if found to have occurred will be dealt with according to the procedures set
down by the University.
You must submit your coursework with the appropriate bar-coded coursework header sheet.
To print the header sheet go to http://banner.gre.ac.uk/ and login using your Bannerweb ID
and PIN. Follow the instructions and print the resulting header sheet for this assessment item.
The Header Sheet ID is the six-figure number shown in the title box shown above.
Please note that you cannot submit a coursework with a copy of a header sheet printed by
another person with their Banner ID and PIN. All such courseworks will be rejected.
Courseworks submitted after the deadline published by your centre and without an extenuating
circumstances claim will receive a ZERO grade. You may submit an extenuating
circumstances form however in order to request that you be granted a deferral of the
coursework to the following assessment point.
No extensions to published deadlines will be given so please do not ask your tutor.
If you have extenuating circumstances you may submit your coursework up to two weeks
after the published deadline without penalty but this is subject to acceptance of your claim by
the School Extenuating Circumstances Panel. If your claim is rejected then you will receive a
zero grade for your work.
Coursework submitted more than two weeks late will be given feedback however a grade of
non-submission will be awarded regardless of any extenuating circumstances. However, if
your Extenuating Circumstances claim is accepted then the Extenuating Circumstances Panel
will recommend to the Progression and Award Board that you be permitted to retake a different
item of assessment at a future assessment point.
Page 1 of 6
The aim of this assignment is to undertake a range of tasks involved in designing a Engineering
Institute database system. It is mainly based on the accompanying specification. Some of the
information provided may be of little relevance, and other information, which is required, may be
missing. Where information is not available you should make reasonable assumptions.
Projects Inc. is an engineering firm with approximately 500 employees. A database is required to keep
track of all employees, their skills, projects assigned, and departments worked in. Every employee has
a unique number assigned by the firm. It is required to store his/her name and date-of-birth. If an
employee is currently married to another employee of Projects Inc., then it is required to store the date
of marriage and who is married to whom. However, no record of marriage need be maintained if the
spouse of an employee is not an employee of the firm.
Each employee is given a job title (e.g. engineer, secretary, foreman, etc.). We are interested in
collecting more data which is specific to the following types; engineer and secretary. The relevant data
to be recorded for engineers is the type of degree (e.g. electrical, mechanical, civil, etc.) and for
secretaries, their typing speeds. An employee does only one type of job at any given time and we need
to retain information material for only the current job for an employee.
There are 11 different departments, and each has a unique name. An employee can report to only one
department. Each department has a phone number. To procure various types of equipment, each
department deals with many vendors. A vendor typically supplies equipment to many departments. It
is required to store the name and address of each vendor, and the date of last meeting between a
department and a vendor.
Many employees can work on a project. An employee can work in many projects (e.g., East London
Crossing, Channel Tunnel, Millennium Dome, etc.), but can be assigned to only one project in a given
For each city, we are interested in its state and population. An employee can have many skills (e.g.
preparing material requisitions, checking drawings, etc.), but he/she may use only a given set of skills
on a particular project. (For example, an employee Smith may prepare requisitions for the East London
Crossing, and prepare requisitions as well as check drawings for the Channel Tunnel).
An employee uses each skill he/she possesses in at least one project. Each skill is assigned a number.
A short description is required to be stored for each skill. Projects are distinguished by project
numbers. It is required to store the estimated cost of each project.
Page 2 of 6
Initially the following applications are planned:
A1. List the names of all employees who work on projects located in cities with a population over
A2. List the names of all projects with estimated cost over £2,000,000 and employees with a
Mechanical Engineering degree.
A3. List the names of all married employees and their spouses who are assigned to the "East
London Crossing" Project.
A4. List the names of all cities which have secretaries assigned to their projects with typing
speeds over 80 wpm.
A5. Create a report listing of names and addresses of all employees of a specific given skill. The
given skill should be picked from a drop down menu (e.g. Combo Box). [ref. D8 below].
A6. For any given equipment type, List of names and addresses of all suppliers who supply that
equipment. The equipment type name should be picked from a drop down menu (e.g. Combo
Box) [ref. D9 below].
Page 3 of 6
Please note that you cannot use a DBMS different from the one adopted by your centre and
approved by your tutor of the course.
D1. One A4 page, state clearly any assumptions (i.e. Enterprise or Business rules) that you make
about the data; in particular, noting any information that you believe should be included, but
is not mentioned in the outline specification.
D2. One A4 page containing the conceptual data model diagram (i.e. an Entity Relationship
Diagram, using the Chen notation) for the system. Your diagram should show:
Relevant Entity Type,
You only need to show the following attributes in the conceptual model: a Primary Key
for each entity; any multi-valued or derived attributes; Derived attributes & Relationships
Relationship Type with a role name (plus relationship attributes if any),
Structural constraints for each relationship (both cardinality and participation).
Note: if you show attributes other than the Primary Keys (e.g. Foreign Keys) then you will be penalised.
D3. For the above model, produce a relational schema (i.e. transforming the conceptual data
model into a logical relational schema) on one A4 page. Your Relational Schema should
All entity and relationship types that are potential for a relational table.
For each potential table identify the primary key and any necessary foreign keys and all
Show the links between the tables2 (e.g. a table-relationship diagram produced by MS
Access) or, for each table, describe any links to other tables in SQL statement(s).
D4. Normalisation check: You need to check your produced Relational Schema above for 3rd NF.
If it satisfies 3NF criteria then you ONLY need to include the statement “The Relational
Schema satisfies 3NF criteria”. If your schema does not satisfy the criteria of 3NF then you
need to reproduce your schema in 3NF. You Do NOT NEED to show the steps (i.e. process)
D5. Create a DB for the above schema in an appropriate relational DBMS and populate each table
with typical records to clearly demonstrate the application results. You do not need to
produce a snapshot of the tables.
D6. Write the SQL code only (i.e. no form or report) for applications A1-A4 above. —DO NOT
USE the QUERY BUILDER tool available with your DBMS. Code produced automatically
by the wizard (i.e. tools) will be awarded ZERO.
D7. Using the appropriate tool from the chosen DBMS, create a simple form to enable end user to
register on-line. A new user will register their email address, and password. Duplicate email,
password combinations will be rejected. The form should have at least two buttons on it—
one to commit a new user to the database and one to exit the form.
To avoid cluttering the Conceptual Data Model with many attributes and to improve clarity, other attributes can be
shown in the logical model (i.e. or listed in the relational schema). Any extra attributes which are not required to be
shown on the CDM (as indicated above) will be ignored. Please note that presenting Foreign Keys at the CDM
diagram is wrong and students will be panelised if they do so.
You can show the links between Primary and Foreign keys on your relational schema as arrows starting from the FK
and ending at the PK, as shown in the example below.
Doctor (DocNo, DocName, BirthDate, Address, City, PostCode, Tel_H);
Doctor_Qualification (DocNo, QualName, QualDesc, DateAwarded); /*DocNo referencing Doctor.DocNo */
Page 4 of 6
D8. Using the appropriate tool from the chosen DBMS, produce a report for application A5
D9. This is for application A6 above. Using the appropriate tool from the chosen DBMS create a
master/detail form to perform this application.
D10. You are required to implement all above applications as well as submitting a one A4 page
containing the SQL statements only required by the sample applications above. You need to
test all your queries with sample data and make sure that all your queries produce some
answers. You will be asked by your tutor, during your demo, to run some of these queries.
You do not need to produce forms or reports for submission. Forms and reports will be
assessed during the demonstration of your cswk.
D11. A 3.5” disk or a CD labelled with the students name, UoG unique ID & course code
containing the implementation of the schema developed above with the SQL code of all the
sample applications above. You should use Microsoft Access DBMS (or any appropriate
relational DBMS3) for the implementation of your system (the record source of all forms
should be based upon SQL statements and NOT on built queries/wizard/query
generator), and the “.mdb” file must be submitted on your disk.
D12. A completed marking schema sheet (template provided by UoG to the local tutor). The sheet
will be signed by your tutor saying that a working prototype has been implemented and
demonstrated by the student. This will be supplied by the local tutor and not by the
Additional Coursework Submission Requirements
The total pages for submission should not exceed SEVEN/EIGHT A4 pages. Courseworks that do
not adhere to this constraint not be accepted. You should adopt the following organisation for your
1. One A4 page for Conceptual Model.
2. One A4 page for assumptions and business rules.
3. One A4 page for the mapped Relational Schema showing links between FKs & PKs.
4. One A4 page for Normalisation declaration (or any normalisation related activities) (ref D4
5. One A4 page for presenting SQL code (ref. D10 above).
6. One A4 page for your bar coded header sheet.
7. A completed marking schema sheet produced by your tutor (ref. D12 above).
8. A floppy disk or a CD with your implementation. Your disk/CD should be clearly labelled with
your name, UoG No. & name of centre (ref. D11 above).
Using Oracle, Access or SQL server, etc. makes no difference for achieving the aims of the coursework. Oracle users
should submit on their CDs the following: a file containing all SQL scripts that are used in creating and/or populating
the database objects; a file containing the SQL scripts used to answer the sample applications, all oracle forms and
reports files (i.e. …… .fmb and .rdf files).
Page 5 of 6
Note: Students Will Fail the Coursework If:
Submit No Implementation, Do Not Demonstrate The Work To Their Tutor Or Do Not Attempt
Conceptual Model 40%
Many possible models, but the model should identity major entities and relationships.
Identifying Entities with a proper unique identifier: 15%
Reasonable Assumptions: 5%
Assumptions are to clarify unclear business rules or procedures. Assumptions are to
reflect common-sense and student’s ability to make the right judgement when
information is missing. Any unreasonable assumption that aims to make the
design simpler and compromise or ignore business rules gains no marks.
Identifying Relationships, Generalisation/Specialisation hierarchies 10%
Cardinality and Participation Constraints of relationships 10%
Relational Schema (i.e. Logical Model) 20%
A list of candidate attributes for each table as specified in the requirements. 5%
A PK may be identified by an underline (a common practice) and a FK by any other
means as described by the candidate (e.g. double underline, italic style, different
colour font, etc.).
Produced Relational Schema (mapping): 10%
The focus here is on the link between Primary keys (PKs) and Foreign Keys (FKs).
Use the standard notation for the relational schema and draw arrows to show the links
between the tables. The arrows should originate from the FK attribute and end at the
referencing attribute as shown below:
<table1Name> (PK Identifier, attribute1, attribute2, FK_attribute, etc.);
<table21Name> (PK Identifier, attribute1, …, FK_attribute, etc.);
Points to check: placements of FKs in correct target table(s), need to create
new tables, and correct handling for any superclass/subclass constructs.
Normalisation Check 5%
The produce logical schema is expected to be in 3rdNF. A candidate may normalise
their tables to produce the 3rdNF version, or (if the schema in 3rd NF, an assurance
statement that the produced relational schema is in 3rdNF is essential).
Implementation and SQL Applications 30%
A candidate should implement the Database on the target DBMS and populate the
each table with sample data (3-5 records each). 5%
Implementing the correct constraints (PK/FK links, Null allowed or not, etc.)
Implementing the above applications as specified (e.g. producing SQL code,
passing parameters, etc.). 25%
Quality of presentation/demonstration 10%
Presentation of your screens (i.e. navigation) and reports; quality of your
deliverables (Conceptual and logical relational model, assumptions, SQL code,
etc.). This will be awarded during the demo.
Page 6 of 6