Database Phase Test Appendices | Good Grade Guarantee!
CTEC2908: Database Phase Test Appendices – Model, Database and ScenarioPeter Bounds, De Montfort University, 2018 Page 1Appendix 1 – VoD database modelActor
assigned1played in1Rolemplayed bymfilmId actorId
characterNamefilenamefirstnameUKReleaseDatedirectorFirstnamegenrelastnamedirectorLastnamedateOfBirthnationalitybiographydescriptiondescriptionfirstAppearancetitleruntimeClassification (classId, description)Film (filmId, title, directorFirstname, directorLastname, genre, UKReleaseDate, filename, runtime,class)Actor (actorId, firstname, lastname, dateOfBirth, nationality, biography)Role (filmId, actorId, characterName, firstAppearance, description)CTEC2908: Database Phase Test Appendices – Model, Database and ScenarioPeter Bounds, De Montfort University, 2018 Page 2Appendix 2 – Test databaseClassification
Universal: Suitable for all
Parental Guidance: General viewing but some scenes may be unsuitable for youngchildren
Suitable only for persons of 12 years or over
Suitable only for persons of 15 years or over
Suitable only for persons of 18 years or over
The Dark Knight
The Man in the IronMask
Back to the Future
continuedCTEC2908: Database Phase Test Appendices – Model, Database and ScenarioPeter Bounds, De Montfort University, 2018 Page 3Actor
…Born in West Covina, California, but raised in New York City,Tim Robbins is the son of former The Highwaymen singer GilRobbins and actress Mary Robbins….
… With an authoritative voice and calm demeanor, this everpopular American actor has grown into one of the mostrespected figures in modern US cinema.…
… she wanted to start acting at an early age and attended theNeptune Theater School. She began her career at the age of10 on the award-winning television series Pit Pony…
… the only child of former comic book artist George DiCaprioand Irmelin DiCaprio. His parents signed him with a talentagent when he was a child, and DiCaprio began appearing ona number of television commercials and educational shows.…
Intelligent banker that was falsely convicted of murdering his wife isan inmate at Shawshank Prison. Andy dreams of freedom.
Ellis Boyd ‘Red’Redding
The prison inmate is a convicted murderer. He has a reputation forsmuggling contraband and is a friend of Andy Dufresne.
He is an extractor that enters people’s dreams and steals theirsecrets. DiCaprio was the first to be cast in the film.
King Louis XIV
The identical twin of Philippe of Gascony is a handsome but crueland ruthless ruler.
Philippe of Gascony
The identical twin of King Louis XIV is kind and compassionate,unlike his twin brother. He looks exactly like his brother when he isnot wearing the iron mask.
CTEC2908: Database Phase Test Appendices – Model, Database and ScenarioPeter Bounds, De Montfort University, 2018 Page 4Appendix 3 – Bounds Institute of Technology (BIT) ScenarioDraw an Entity-Relationship Diagram (ERD) and produce a set of normalised TableTypes for the following scenario below. There will be questions about your ERD andTable Types in the phase test. You can bring your design and the scenario to thetest.Hint: The database design should contain at least the following table names found inthe scenario: Employee, Department, PriorityLevel, ProblemType, OverdueTicket,Ticket, PriorityTicket. Extra tables can concatenate their related table namestogether, e.g. DepartmentEmployee. An extra qualifier may need to be appended tothe concatenated table name to distinguish it from another table with otherwise thesame name, e.g. DepartmentEmployeeManager.The IT department of an organisation known as the Bounds Institute of Technology(BIT) requires a relational database to log and keep track of their helpdesk activities.When an employee of the organisation makes an enquiry, complains about a fault, orreports a problem, this is initially to be logged as a “ticket”. The database will need tostore a few contact details about employees, such as name, employee number,phone number, email address, so that they can be contacted when the problem isresolved, or when further details are needed. Each ticket is given a ticket number toidentify it from all others. This must be stored together with the date and time whenthe initial incoming support request was logged, and it must also be possible to tracewho was responsible for creating the ticket, i.e. the employee who took the helpdeskrequest. A description of the enquiry or problem must also be stored. In practice,many problems of the same type may re-occur over time and their description shouldnot be recorded more than once.Some common, frequently occurring problems can be, and are, solved immediately.These are problems such as “I forgot my password”. The helpdesk staff refer to a setof online Frequently Asked Questions (FAQs) to help them deal with such commonproblems (“Try the password self-service link to reset your password”) and theseresolve many of the support requests immediately. In such cases, the ticket is“closed” (a closure date and time are recorded). It will be assumed that the personclosing such a ticket was the person who created it, so this does not need to berecorded. The FAQs are not stored in the database being designed here.All other incoming requests, and any “common” requests where the FAQs do notlead to the ticket being closed immediately, are allotted a specific priority level. Thereare three such priority levels (high, medium or low) depending on how critical theproblem is. “all the computers in the informatics department have just crashed”would be a “high” priority rating. “The printer is jammed” might get any of the ratings,depending on whether there were several other available printers on the network, orCTEC2908: Database Phase Test Appendices – Model, Database and ScenarioPeter Bounds, De Montfort University, 2018 Page 5whether the documents in question were needed for an important client in tenminutes’ time. Each of the three priority levels (there is discussion about whetherthere should be more or fewer levels, but nothing has yet been decided) has certainimplications. The system is required to flag up and report “overdue” tickets. How itachieves this is an application issue that need not worry you, provided that yourdatabase design caters for the required data that is to be processed.Currently, a high priority ticket must be “closed” within one hour of the initial supportrequest being logged. A medium priority ticket should be closed within eight workinghours, and a low priority request within sixteen. These timings may, of course,change in the future if they are considered to be unreasonable or over-generous inpractice. As well as these deadlines (numbers of hours), the database must record adescriptive field for each priority level (action to be taken where the deadline is likelyto be passed, emergency mobile numbers for technical staff, outside contractors,etc.).For all tickets that have been allocated a priority, the database will store a closuredate and time, as for “common” problems. However, if the solution is found, thedatabase will also store an explanation (description) of how the problem was solved,which employee(s) carried out the work, and which one signed off (“closed”) theticket. The storage of the description of the solution method is important, becauseproblems which recur can be solved more quickly, perhaps becoming the subject ofone of the FAQ “common” problems.A separate descriptive text must be stored wherever a ticket was not closed by thedeadline, for example where a high priority ticket was not closed until two hours fromthe initial logging. A record of the employee who wrote the description of the overdueticket, and of the senior manager to whom a report of the overdue ticket was sent,must also be stored.Management, who know the total cost to the organisation of the helpdesk, would liketo break down that figure by finding out how many of the tickets (support requests)come from any given department. Each employee of the organisation is assigned toone department.Management have provided examples of the sort of information they would beinterested in obtaining from the finished database:• What was the number of tickets generated in a given week by the departmententitled “Engineering”?• For all closed tickets that were overdue, list the details of the employees whorequested, logged, worked on and signed off the ticket.• List details for employees who received a report of an overdue ticket on whichthey were the employee actually doing the work.
QUALITY: 100% ORIGINAL – NO PLAGIARISM.
- **REMEMBER TO PRECISE PAGE NUMBER**
- Hit The Order Button To Order A **Custom Paper**