Database Systems Assignment -

Learning outcomes -

• Describe in brief how a business operates.
• Utilise standard graphical notations for communicating the form of the database to the customer and the programmer in a standard manner.
• Analyse the requirements for business database, create and query it utilising the SQL language in a database management system (this year XAMMP has been explained and MySQL).
• Elicit the database specifications from brief documents.
• Perform the database conceptual requirements analysis.
• Describe the logical diagrammatic model of the data and how to normalise it.

Brief Assignment Description - This assignment requires students to work in pairs towards describing a business case study, and its database needs considering both the conceptual and normalised logical model of the data.

Detailed Assignment Description -

You are building an online business database of your choice. The database must have the followings elements:

• A manner for the customer to login to the online system.
• The list of product and their attributes (e.g. price, which should range between £1.00 to £100).
• Customer transaction history (e.g. what they bought, how many items, and all the data relevant to the transaction).

You need to produce a Written Report (1500 word max) which includes the title of the report, the authors (and percentage each has contributed to its creation) and the following:

A. Customer Brief - this is an essential part of both the project development and the contract with the client as it helps guarantee that the work for a project will be done according to specific guidelines and expectations of the client. Your brief should include:

• An Overview of what the business does (a short paragraph of max 500 words)
• A Concept Table (who, when, where, why, how)
• A conceptual model of the business and its attributes (e.g. main tables and columns needed in the database) displayed visually in two manners as: Axis Technique and Conceptual ER model.
• A definition of all the key-works utilised by the business and their meanings.

B. Show how from the conceptual ER model you have produced the logical ER model and normalised it. In the report you need to present:

• First logical ER model (this is likely to be the same as the Conceptual ER model, where you have ensured it has all the columns, but might not have all the keys)
• Logical ER model (1NF)
• Logical ER model (2NF)
• Logical ER model (3NF)

C. Build the database in XAMMP (in the report you should provide the screen-shots of the database creation)

• Create the database.
• Create all the tables, with columns and keys.

D. Code the following queries in XAMMPS (in the report provide the screen-shots of the queries code and results/print out):

• Produce a list of items which are low stock (e.g. below 20 items in the warehouse).
• Produce a list of customers that live in specific UK city that bought items above £20.00.
• List the products of the company that have sold the most in the last month.
• Produce an invoice on that day (date) for a specific customer (customer name and address) that has more than one item and more than one quantity, and the total to be paid by the customer

E. Composition of report

• Include a table of content and figures
• Include all screen-shot and graphics. These must be readable, that is of good quality and appropriate for the size of the paper.
• Ensure spelling and grammars have been taken care off.