University of Westminster Computer Science & Software Engineering COURSEWORK SPECIFICATIONS 5COSC002W DATABASE SYSTEMS COURSEWORK (2019/2020) Module leader Francois ROUBERT Unit ...

COURSEWORK SPECIFICATIONS

Assessment regulations

For detailed information regarding University Assessment Regulations on how you are assessed, penalties and late submissions, what constitutes plagiarism etc. please refer to the following website:

http://www.westminster.ac.uk/study/current-students/resources/academic-regulations

Penalty for Late Submission

If you submit your coursework late but within 24 hours or one working day of the specified deadline, 10 marks will be deducted from the final mark, as a penalty for late submission, except for work which obtains a mark in the range 40 – 49%, in which case the mark will be capped at the pass mark (40%). If you submit your coursework more than 24 hours or more than one working day after the specified deadline you will be given a mark of zero for the work in question unless a claim of Mitigating Circumstances has been submitted and accepted as valid.

It is recognised that on occasion, illness or a personal crisis can mean that you fail to submit a piece of work on time. In such cases you must inform the Campus Office in writing on a mitigating circumstances form, giving the reason for your late or non-submission. You must provide relevant documentary evidence with the form. This information will be reported to the relevant Assessment Board that will decide whether the mark of zero shall stand. For more detailed information regarding University Assessment Regulations, please refer to the following website:

http://www.westminster.ac.uk/study/current-students/resources/academic-regulations

Coursework Specifications Part A: Conceptual ERD

[35 Marks]

Part A Project Brief: AKKTIVATION

AKKTIVATION is a large leisure and fitness company that has branches all around the South East of England. AKKTIVATION’s mission statement is to give the opportunity to everyone to get active and fit in a stimulating and supportive environment. To this effect, AKKTIVATION provides first-rate facilities for people to undertake fitness training on their own, to take fitness classes in groups, or to just simply enjoy swimming in high-quality pools. Every AKKTIVATION branch features a swimming pool (25 metres long with 6 lanes), a fitness suite (i.e. a standard gym with specialised fitness equipment for working out), and several fitness studios (i.e. typically wooden-floored rooms with mirrors specially dedicated to fitness classes).

General customers can use the gym and the swimming pool as they wish, in a very straight forward manner. They just need to set up a payment balance with AKKTIVATION and every time they come, their attendance to a session is registered and their payment balance is debited by the price of a session. To credit their balance, they just need to pay in person at the counter. This way, they can swim in the pool or work out in the gym at their best convenience, in the simplest possible manner.

In order to benefit from additional exclusive services, customers can sign up for an AKKTIVATION membership and become members. AKKTIVATION members are required to pay a membership fee and to provide data about their physical characteristics (such as height, weight, existing conditions, etc.) and can set up fitness objectives, if they wish to. The key advantage from being an AKKTIVATION member is essentially the ability to book sessions in any AKKTIVATION branch. Members can place bookings for three types of sessions: fitness classes (such as yoga, pilates, body balance, Zumba, etc.), swimming lessons (with an instructor, to learn how to swim or improve your swimming) or one-to-one training sessions in the gym (with a personal trainer, to work on a particular aspect of your fitness). If members want to benefit from one-to-one training sessions, they must first book a one-to-one induction session: it is an introductory session during which a personal trainer evaluates the member’s needs, show them how to do specific exercises, and authorises the use of special equipment. The special equipment that is authorised after each one-to-one induction session needs to be carefully recorded.

Regarding the staffing of AKKTIVATION personnel working locally at branch level, there are essentially three categories. First, personal trainers conduct one-to-one training sessions in the gym to support the training regime of members and educate them about fitness (and therefore also conduct one-to-one induction sessions). Second, the role of support staff is to maintain the facilities and particularly the equipment available in every branch to make sure it is in pristine condition. And finally, the third group consists of instructors who deliver booked sessions: swimming instructors give swimming lessons in the pool and fitness instructors teach fitness classes in the fitness studios.

Part A Questions

You have been hired by AKKTIVATION as a
database architect
to undertake a database project to support the data needs of the company. Your job in this first part is to produce a high-quality
CONCEPTUAL ENTITY RELATIONSHIP DIAGRAM (ERD)
and to produce a
data dictionary
to document and support your model.

⚠
️


Prefix the names of all entities and attributes with your id number starting with w.

Produce a complete
CONCEPTUAL ERD
for AKKTIVATION.

It needs to include all the
entities,
relationships,
multiplicities,
attributes
and
primary keys
that you have identified and needs to fit on one page of the report.

Create a
Data Dictionary
to document how you identified the
entities
for AKKTIVATION. To achieve this, fill in the 2 tables below to summarise and explain how you identified the entities for your data model.

For more information, please refer to page 510 of the 6^th
edition of the Connolly’s textbook.

Create a
Data Dictionary
to document how you identified the
relationships
and
multiplicities
for AKKTIVATION. To achieve this, fill in the table below to summarise and explain how you identified the relationships and multiplicities for your data model.

For more information, please refer to page 513 of the 6^th
edition of the Connolly’s textbook.

Create your own separate table if you identify complex relationships to identify the entities involved and provide justifications.

Create a
Data Dictionary
to document how you identified the
attributes
and
primary keys
for each entity for AKKTIVATION. To achieve this, fill in the table below to summarise and explain how you identified the
attributes
and
primary keys
for your data model.

For more information, please refer to page 516 of the 6^th
edition of the Connolly’s textbook.

Interactive FAQ

In order to provide you with the support you may require as a Database Architect and answer any questions you may have about the AKKTIVATION brief, an interactive FAQ is offered on padlet, a Web tool to build a virtual wall / bulletin board. This allows you to ask specific targeted questions to the AKKTIVATION Managing Directors about the AKKTIVATION business so that to improve and refine your conceptual ERD and for these questions and their answers to be shared with the entire class. The padlet is available on https://padlet.com/f_roubert/AKKTIVATION_FAQ

Part A Marks Allocation

Part A
will be marked based on the following marking criteria:

Coursework Specifications Part B: Logical ERD, SQL & PHP

[65 Marks]

Part B Project Brief: AudioVizzion

AudioVizzion is a large retail chain that offers optical and audiology services and essentially sells spectacles and hearing aids to the public. AudioVizzion operates globally from a large number of branches located all around Britain. Every branch offers AudioVizzion customers the opportunity to get tested on their vision and/or on their hearing before being able to purchase what the company refers to as Visual Devices (i.e. frames and lenses for glasses) and/or Hearing Devices (i.e. hearing aids).

Part B Questions

You have been hired by AudioVizzion as a
database architect
to undertake a database project to support the data needs of the firm. In this second part, you are given a conceptual data model for AudioVizzion (figure 1) and your first goal is to
map it
onto a high-quality
LOGICAL ENTITY RELATIONALHIP DIAGRAM (ERD)
to logically represent how the key business data needs can be organised as a set of interrelated tables that can then be implemented. These tables need to be interconnected according to the strict rules of the relational model to be implementable. You also have to create tables in SQL and insert some data in these tables.

⚠️

Prefix the names of all tables and attributes/columns with your id number starting with w.

Produce a complete
LOGICAL ERD
for AudioVizzion (mapped out from the Conceptual ERD given on
figure 1). This needs to include all the
correct tables, relationships, multiplicity constraints, attributes, primary keys
and
foreign keys.

This needs to fit on one page of the report.

Provide a
step-by-step guide
explaining how you produced the AudioVizzion
logical ERD
i.e. how you
mapped
the AudioVizzion conceptual ERD (figure 1) into a full
logical relational schema. To achieve this, write a series of numbered
bullet points
(e.g. [1], [2], [3], etc.) to explain how you applied the
10 rules of the Logical Data Modelling Methodology
(see Lecture 04 and Lecture 05), and in which order, to convert the given Conceptual ERD into a Logical ERD.

This needs to fit on no more than two or three pages of the report.

Write SQL code to
CREATE
in the MySQL RDBMS
the
3 tables
that should result from
the mapping of the entity Device and its specialised entities
(see figure 1).

Follow
the instructions below:

The 3 tables should all have the correct constraints for primary keys, foreign keys and alternate keys.

Include the
SQL code
used to create the tables and
screenshots of the structures of the tables.

The names of all the tables and all columns must be prefixed with your id number starting with w.

The screenshots should clearly show your student id numbers (in the names of the tables and attributes and also right above the tabs for the structure of the table and on the left hand-side in PHPMYAdmin,
see figure 2).

Write SQL code to
INSERT
in the MySQL RDBMS
records in the 3 tables that you have just created.

Follow
the instructions below:

The SQL statements needs to
insert the data related to the following products:

One “Emporio Armani Ultra-Light frame” for glasses.

Brand: Emporio Armani.

Model: Empo324.

Description: Brand new grey and blue ultra-light frame, latest trend in 2019.

Availability: in stock.

One “Optimo Single Vision Lens” for glasses.

Serial Number: opto456321987.

Vision type: Single vision for short-sightedness.

Tint: clear.

Thinness Level: ultra-thin.

Description: Optimal single vision lens, 2020 style, anti-scratch and anti-shock.

Availability: in stock.

One “Phono Titanium Digital Hearing Aid” to support hearing.

Make: Phono Titanium.

Model: phono2021.

Description: Ultra-sensitive digital hearing aid, adjustable through an App.

Availability: currently being ordered.

Include the
SQL code
used to insert the records in the 3 tables and
screenshots of the content of the tables.

The screenshots should clearly show your student id numbers (in the names and of the table and attributes and also right above the tabs for the structures of the table and on the left hand-side in PHPMYAdmin,
see figure 2).

Part B Marks Allocation

Part B
will be marked based on the following marking criteria:

___________________________________________________________________________________

⚠️
Key Requirements for the entire coursework

Only
UML notations
are accepted, as introduced in this module.

You need to
prefix
all your entities and attributes with “w + the 7 digits of your ID number” as provided by the University.

For example, if my name is Francois Roubert and my ID number is w1234567, when I identify the entity “Module” and its attributes “moduleCode”, “moduleName” and “moduleType”, I will have to represent it this way

_____________________________________________________________________________________

Figure 1: AudioVizzion Conceptual ERD to be mapped to a LOGICAL ERD (PART B)

Figure 2: screenshots for the structure and content of the tables in part B questions 7) and 8)