Reference no: EM132240291

Assessment Task

Task Summary:

In this assessment, the student will be required to use their skills and knowledge to design a database for a particular client. Information regarding the client's business will be provided to the student in Task Details.

Task Scenario

The software development company of which you are a lead developer has been approached by a local video store called *Epic Videos" with a business goal to convert their existing manual system to a computer based system. Overall this involves the development of a new application but your task as part of the development team is design and implement the database in the SQL Server RDBMS.

Epic Video as any other typical outlet hires out movies both VHS and DVD and may keep up to 8 copies of popular movies. Customers are only allowed to rent a maximum of 6 movies at any one time and may be blocked from further rental in the case of overdue or unpaid rentals. Epic videos get very busy on weekends normally having two or more staff processing customer rentals.

Some of the key duties of this staff are:
1. Search for movies based on title or part thereof, genre or format (DVD/VHS)
2. Search for customers based on name or part thereof or phone number.
3. View rental information and history of particular customers identifying any late rentals.
4. View movie information including all copies of a movie and their rental status. S. Identify lost, stolen, damaged or obsolete movies.

Additional duties may include:
1. Adding, removing or modifying customer details
2. Produce reports of customer rental history highlighting overdue rentals and unpaid late charges.

Epic Videos has a number of business rules and policies that are to be enforced by any new system:
1. All movies can be rented overnight, 3 days or weekly with appropriate fee increase.
2. Storing the customers rating of the rental on a 5-star basis.

Task Details:
You are required to perform the database design for the customer and following all steps and implement the above system in an RDBMS e.g. SQL Server and/or MS Access

This requires completion of part A through F described below:

PART A

Your task in part A as the lead developer is to understand the above information regarding the business and perform an interview with appropriate staff at Epic Videos to refine the database requirements (a role play organizing a time With your trainer to achieve this). Using this collective information (identifying the hints provided above) develop a conceptual model of the new database.

Assessors can provide typical information, but should only elaborate on the request or questioning initiated by the student. This is to ensure each student can communicate effectively with clients.

PART B

Based on this conceptual model you are required to formalize this in a logical database model refining the design using normalization.

Note:
A logical design requires the production of an ER diagram (showing all relationships) and supporting data dictionary (with name, data types, attributes and any comments e.g. validation rules).

As part of your model these keys points should be identified:
– Identify primary keys of each table considering the use of automatically generated values for these.
– Identify the modality and cardinality of all relationships between entities and the foreign keys to support these.
– Ensure your model supports correct referential integrity rules and constraints and document in data dictionary.
– Ensure your model identifies data integrity or validation rules and document in data dictionary.
– Identify where required the use of indexes and list in data dictionary.

Once corn leted students need to submit database desi n to assessor for evaluation.

PART C

You are required to implement the physical model of the database in SQL Server correctly.

1 Firstly, you must identify security requirements and create necessary SQL Server logins to your database.

2. Secondly you must create the database and translate your logical model into the necessary physical implementation.

Note:
– The business rules stated there can be multiple staff taking customer rentals, so ensure you identify correct roles and privileges for this login information.
– Database logins and database users are not the same ensure your login maps correctly to the required database user considering SQL Servers implementation of the owner of created database entities.
– Correct translation of data types and attributes primary keys, identity specification, creating relationships between tables and any necessary constraints to validate data.
Once completed student need to test the database using a range of data.

PART D

For each of the key duties listed in the task detail develop SQL queries to enable the extraction of this information. Note:

It would be best to either save a query in MS Access or develop the appropriate view in SQL Server as the basis of the report.

Once completed student need to test the queries on a range of data.

PART E

Write a short summary of your knowledge of database back-up and recovery that may apply to SQL Server specifically.

PART F

Using MS Access to link to your SQL Server database and using point 1 of additional duties as a guide generate a simple form for the manipulation of customer details.

Using the above MS Access database generate a simple report using point 2 of additional duties and the query you developed in point 3 of key duties.

Note:

You should use the queries developed in Part D

Assessors are to ensure each assessment is conducted in a simulated work environment.

This will require you to:

– Allow for distractions such as background noises, ringing telephones, etc. As this is a business service unit, a simulated environment may include a closed meeting room, office, or even the classroom (if it represents a working office environment).

– Ensure each student is dressed appropriately / professionally

– Ensure you act as a supervisor, and do not interfere with the assessment. In the event that the assessment is looking to impact on the safety of a student or bystander, the assessment must be stopped immediately

– Ensure each person involved with the assessment role-plays effectively as per real-life scenario.