Paper, Order, or Assignment Requirements
Module Intended Learning Outcomes
On successful completion of this module you will be able to:
Knowledge and understanding
1. Demonstrate an understanding of the process of analysing data requirements of a business based on a given case study
Intellectual, practical, affective and transferable skills
2. Design and produce data models and based on these models implement a working relational database; Use one professional standard development and implementation package effectively with a grasp of its technical limitations.
• The word limit for this part of assignment is 2,500 words.
• Diagrams should be drawn using recommended CASE tools.
• The screenshots required should be clearly printed and readable, carrying your own SID.1244234 number. Illegible screen prints will not be awarded marks.
Based on the same Problem Statement for the Assignment One (provided below) you are required to complete the following tasks:
1. Identify and list all the entity types and attributes from the case study. (12%)
2. Present in diagrammatic form an initial ER model using UML notations. (12%)
3. Normalise all entities to 3NF and explain the process. (16%)
4. Based on the initial ER model (resolve problems if any) and results from normalisation process produce a logical entity relationship model, annotated with extended ER modelling notations. (10%)
5. Design and present a database schema in tabular format consistent with the logical ER model. (10%)
6. Using MS SQL Server, implement the database schema above and populate tables with sufficient data for query testing. Include screen dumps showing execution of all CREATE TABLE statements and the populated tables. (20%)
7. Design and construct a minimum of FIVE reasonably advanced queries to extract useful information from the database system. Provide screen dumps to show the results of each query in MS SQL Server environment, with an explanation of the results. (20%)
Note: Proper documentation of your assignment will also be rewarded extra points (see the Marking Schemes). Therefore, make sure that your documentation skills are up to the standard required.
Marking Scheme for Assignment 2: Database Design and SQL
C1 List of Entities and attributes 12%
You should have produced a list of entities and attributes based on candidate entities and attributes, with evidence of how final lists have been reached.
C2 Initial ER diagram 12%
The initial ER model should include the entities and attributes identified in the previous task, with all relationships correctly modelled and annotated. The entities should have at least the Primary Key identified and highlighted. The notation used should be the one recommended by your tutor.
C3 Normalisation process 16%
The process of normalisation should be done using one of the following methods: 1 starting with a single list of attributes representing an entity and break it down step by step to entities of 3rd normalised form; 2 bringing the basic entities identified in earlier tasks individually to 3rd normalised form step by step. Evidence should be provided as for what has been done at each step with a justification.
C4 Final ER Diagram 10%
A comparison should be presented of the initial ER model and the results of normalisation process and any differences in entities and attributes should be identified and resolved in the final version of ER model. Decisions as to what to keep and what to remove should be justified.
C5 Design database schema 10%
The database scheme should be presented in tabular format, with definitions of data type, length, keys and other constraints required for the attributes.
C6 Implement database schema, populate tables with sufficient data for query testing, with screen captures. 20%
All tables should be implemented in the DBMS recommended by the tutor using correct SQL commands and populated with an adequate amount of records sufficient to produce convincing results for the queries in the next task. Screen shots should be provided for all CREATE TABLE statements as well as SELECT * statement for each table.
C7 Query design and database manipulation 20%
The design of the queries should be based on business needs and aims to produce useful results informative to the business. Evidence of “advanced” skills in using SQL commands. Screen shots should be provided for all the queries including the statements and the results produced.
The process of query design should be demonstrated – what information the query aims to retrieve and what data sets are to be used. The codes should be displayed with screen capture showing the evidence that it is being implemented in your own database. The execution of query and results should also be captured.
Students are expected to show an adequate level of SQL skills, indicated by the variety and range of queries and advanced use of SQL functions.
A Web-based Car Sharing System
Car sharing is becoming increasingly popular in the UK and other European countries. It not only reduces the cost of travelling but also lowers your carbon footprint. There are already many interactive websites on the Internet for people to use. More information about benefits of car sharing is available on
The following describes the business processes involved in atypical web-based Car Sharing System although you may find variations. Your designs should be strictly based on the information provided in the Case Study. You can make your some assumptions for the missing information as long as they are within reason, appropriately explained and justified. The following websites are indicated below for your reference so you will understand how the system works.
First of all, all participants (also referred to as the user) must sign up / register with the system with their personal information (e.g. names, sex, year of birth, mobile number, and email address etc.) so that they can be identified. During the registration process the user will have the option to specify a username and password for the account. The registration has to be validated through email – the system automatically sends the user an email to which the user has to respond by clicking the link included in the email. Mobile numbers have also to be verified by text messages.
Once registered, the user can log into the system and register the car(s) to be used for sharing, indicating the make and model of the car, the registration number, the number of seats and if it is properly insured and has a valid MOT certificate. It is possible that the user does not have a car and only wishes to share a car with others.
Offering a trip
When the user has one or more empty seats in his car on a trip he/she can post the following information to the car sharing system.
• Date and time of departure
• Number of seats available for the trip
• If it is a single or return trip
• If it is a one-time trip or a recurring trip
• Departure point (address, city, SatNav postcode)
• Arrival point (address, city, or SatNav postcode)
• Name of towns (maximum three) on the route
Finding for a trip
Anyone registered on the system can search for rides by entering town names of departure destination points or postcodes can be used as the search criteria. It is possible that the user will share the car for a part of journey on the route. For example, if the route is from Chelmsford Railway Station to Cambridge and the towns registered for the route include Stansted Airport the use could just share the car from Chelmsford to Stansted Airport.
The search results that meet the search criteria will be displayed including the trip number, date and time of departure and destination, towns on route, number of seats available, cost of the journey and the driver information including mobile number and email. The user can choose the whole trip or just a part of the trip for sharing.
If all details about the trip and the driver are satisfactory the user can go ahead and click on Contact Driver. An email or a text message will be sent to the driver by the system for confirmation. If the driver accepts the booking of a sharing event the system will reflect it immediately on the website and notify the user who has booked the trip.
Paying for the trip
It is possible for the passengers to pay by cash the right amount of cost indicated for the journey directly to the driver (owner of the car). In this case the driver should report back to the system the total amount he has received for the trip. It is only for calculating how much money is saved for the trip, not for the purpose of taxation.
Another option is to manage the cost of trips online. Regular users can apply for a financial account with the Car Sharing System which is linked to a bank account. It is necessary to start the account with an initial fund £30. When the amount drops below £10 the user will be notified to top it up. If the amount goes above £200 the driver can request for a transfer of a certain amount back to the bank account.
With a Car Sharing account the user can pay for the cost of trips directly via the system. The amount paid will be transferred to the driver’s account. The payment must be made before the trip takes place.
These extra features of the system offer further possibilities for refining the search. For example, the user can set preferences for the car sharing – if it is ok to smoke or carry pets in the car during the journey. Some users may have specific preferences for the gender and age range of other passengers in the car.
More exciting ideas and features may be added to the system if you wish but it is important that you have covered all the required features as indicated above.