Reference no: EM133059843

Unit 4 Database Design and Development - BTEC HND in Computing

Assignment - Database Design and Development

ASSIGNMENT

Aim & Objective
This coursework is designed to demonstrate the broad understanding and knowledge of the module, assessing and evaluating the student's strength and level of analysis; divided into four learning outcomes. The coursework should be submitted as one document in a report format in final submission.

Case Scenario

"ULKOM LTD" is a company that hire bicycles to students, residents, and tourists in Cambridge for many years. "ULKOM LTD" has decided to launch a new scheme dealing with electronic bicycles and electronic scooters due to recent high demands. The scheme will provide short-term and long-term access to electronic bicycles and electronic scooters through self-service terminals placed at convenient 5 locations across the city. For tracking purposes the bikes and the scooters are fitted with a GPS tracking device to identify the locations of the devices and the staff are alarmed if the device is not in any of the 5 locations. The company want to offer subscriptions to members who are 18 years or over, will offer weekly, monthly, and annual memberships. Membership costs £10 a week, £20 a month and £70 for an annual membership.

Information stored for each member includes full name, email, contact phone numbers, residential address, driver's license number, and membership status. A subscriber may only have one active membership at any given point in time. In addition to memberships, visitors or casual users can access the system by purchasing a £5 one-day pass using a credit card.

You have graduated from BTEC L4 College in London, and recently hired by a software company in London. The project manager has asked you to help provide suitable solution using relational database management system to solve the above problems for the new scheme. During the first consultation with the owner of the company, you discussed about the software development life cycle and how to implement the process effectively. During the requirement specification stage of the development, you visited "ULKOM LTD" and had the opportunity discuss users and systems requirements. During the interview you noted that the database system requires different users including customers, managers, and administrative staffs.

Below highlights information obtained during the requirement specification
• The company has 100 bikes and 300 scooters location at 5 different locations in the city.
• Efficient method of storing and retrieving records on bikes and scooters, the customers, and the employees.
• The locations at which the bikes and scooters were hired from and returned to.
• A customer details should include name, age, phone number and address (street, city, and postcode).
• Customers can hire and return electronic bikes or electronic scooters in any of the 5 different locations. Each location has unique code, address (street, city, postcode), date of hire and return date.
• The system should be automated keeping track of the availability, location and conditions of the bikes and scooters.
• The company wants to keep records the unique ID of each bike or scooter, along with its make, model, colour, and size.
• The company keeps record of the hired date, return date and the number of bicycles in stock.
• A user may not borrow more than one bicycle at any given point in time. And each rental transaction is fully automated and records details of the user, bicycle or scooter, pick-up time and location, drop-off time and location, total rental length and the total cost.
• The company has 50 employees at their local depot to manage supply and respond to any issues at the 5 locations in the city.
• The employee on duty should be able track the locations the bikes and scooters. Also, if they have left outside the 5 locations.

The project manager, the database development team and the management team of ULKOM LTD have had a number discussion in order to effectively address the user and system requirements. The project manager has asked you to support the database development team by developing the database management system for ULKOM LTD by following the tasks below.

LO1
Provide clear statement for user and system requirements of the relational database system design. Use appropriate tool (i.e. draw.io or Lucidchart) to produce ERD diagram consisting of at least four interrelated entities. Use any suitable notation (Crow foot or Chen or UML) and show the attributes, primary keys, foreign keys and the relationships between the entities. Produce explanation and illustrations of the normalisations process including 1NF, 2NF and 3NF. Produce the data types of the attributes, including data validation. After producing the user interface statements and ERD diagram, your manager has asked you to produce a diagram of the user interface and explain relationships between the input/output of user interface diagram and the attributes of the entities (i.e. tables). Produce appropriate screenshots and annotations.

Finally, your manager then asked you to produce a report by assessing the effectiveness of the design in relation to user and system requirements.

LO2
After a successful database design, the next step is to develop the database using the structured query language. Using your design as a guide, use a suitable DBMS, for example Microsoft SQL Server 2012 (or above) to develop your database system. The tables created must be populated with records of at least five (5) entries for each table. Produce screenshots of the tables you have created and data you have inserted in the tables. To reflect your understanding of database security and maintenance, you are required to assess how these are ensured in your implementation of the fully functional database system in accordance with users and system's requirements. Produce appropriate screenshots and annotations.

Using Structured Query Language produce query report and ensure your implementation is justified to meet user requirements. Produce at least 8 query reports from a single table and multiple tables. Produce screenshots of the SQL statements and the query reports, including suitable annotations. To ensure usefulness of the report, you should assess whether a meaningful data were extracted from the query report. Produce appropriate screenshots and annotations.

You should discuss the effectiveness of the DBMS you have developed, the query reports and the tools have used in developing the DBMS in relation to user and system requirements, and suggesting improvements.

LO3
It is necessary to test database and in the process of successfully carrying out testing, a test plan suffice. In your report, outline how the system has been tested against users and system's requirements. This test plan preferably to be in a table format illustrating at least six
(6) records tested. Ensure to have "Test Description", "Expected Outcome", "Actual Outcome" as headings. The "Actual Outcome" heading should include a visual representation such as screenshots of results and annotations. Produce appropriate screenshots and annotations of the results of the testing in "Actual Outcome" column.

From the test plan created, you are to explain the different database testing techniques and assess with evidence, one of the testing techniques implemented on your database development. You are required to implement and test the verification and validation process with above query transaction from the database illustrating the understanding of the various features of SQL (update, sorting, joining tables, conditions using the where clause, grouping, set functions, sub-queries etc.). In your report, include recommendations on how you can improve your database development.

LO4
Documentation helps in understanding the concept of database development. To reflect your understanding of technical and user documentation, you are required to produce a fully technical and user documentation for your designed database for the college. Your documentation should include diagrams showing movement of data through the system, and flowcharts describing how the system works. Produce appropriate screenshots and annotations.

Enhancing database development is paramount in completing the development cycle. You are required to assess any future improvements that may be required to ensure the continued effectiveness of the database system.

Attachment:- Database Design and Development.rar