Search our Repository

Order a high quality custom written paper

This revision paper should be used as a source of ideas / reasoning / inspiration for your own research. You should never submit it as your own work. Need something custom and original? Choose from a wide range of academic writing tasks and get the one you need.

Price $7

Pay with paypal

* Once your purchase is processed by paypal you will be redirected back to this page and you'll have the option to download the paper. We'll also send the paper to your paypal email address as proof of purchase.
Download Paper

Basic Database Design And Queries

Question

 Basic database design and queries (5 marks)

Q1. Basic database design and queries (5 marks)

A small shop wants to create a simple database to keep track of its merchandise prices, buyers and purchase orders. For simplicity, a merchandise will be recorded for its name and price, a buyer will be recorded for its name, and order will be recorded for its date and all the items included in the order.

i. Design a minimum database (a database of only essential attributes) to fulfil this purpose, and draw the ER diagram for your design. Indicate on the ER diagram the primary keys and the relationship multiplicities. By minimum we mean that you don't need to add anything that is not explicitly stated in the requirements, unless it's one of your artificially generated keys. For instance, you don't need to include a telephone number or email address for the entity corresponding to a buyer. (1 mark)

ii. Draw the Global Relation Diagram (GRD) corresponding to the ER diagram in the above, indicating all the primary keys and foreign keys. The GRD should be in a similar form to Figure 17.9 (page 554 or 516 for edition 5) of the textbook, but all the attributes should be kept there too. (1 mark)

iii. Write an SQL script (of statements) that generates all the tables for your designed database. (1 mark)

iv. Write an SQL script to insert sufficient records into your tables. Each table should contain no less than 3 records. At least 2 orders should each contain 2 or more items of the ordered merchandise. Screenshots are required for the records of all the tables. (0.5 marks)

v. For a given order (number), write an SQL statement to list all the item names and their corresponding prices for the order (screenshot required). (0.5 marks)

vi. List all the orders by their order number, date, and the name of the buyer who places that order (screenshot required). (0.5 marks)

vii. Write an SQL statement that lists the order (number) and the total price of the corresponding order, for all the orders in the database (screenshot required). (0.5 marks)

Q3. Database modelling - case study (6 marks)

In this part, you are asked to design a database to support the management of a beauty parlour. The major business requirements are summarised below in the Mini Case: Beauty Salon. You are asked to develop a detailed Entity-Relationship model for this mini case. Your ER model should consist of a detailed ER diagram integrated with itemised discussions on the features of the entities and relationships and all the assumptions you made. The ER diagram and the accompanying document should identify keys, constraints, entity types, relationship types, specialisation/generalisation, etc. The ER diagram should in general have more than 5 entity or relationship types and no more than 15. This means that students will have to selectively identify those most important entity types and relationship types for their work and for the fulfilment of the key business functionalities. More concretely, your deliverables should include

i. A list of supplementary business rules or assumptions that underpin your particular database design. When pertinent, link them to the part of your database design with short comments. (1 mark)

ii. A detailed ER diagram for the model, highlighting primary keys, multiplicity constraints, generalisation/specialisation, important attributes, and other pertinent details.

o You must use the same notation scheme for the ER diagram as the textbook, and the ER diagram should be strictly in the sense the textbook uses. We note that past experiences show drawing your ER diagram on Microsoft Powerpoint gives one better control and flexibility when compared with drawing diagrams on Microsoft Word.

o The ER diagram should include, among others, representative attributes for all entity types, proper subclassing, and correct participation multiplicities for the relationship types. It should be meaningfully and well designed, and should also include all relevant and necessary aspects (3 marks)

o Among all the relationships in your design, find a relatively nontrivial one in terms of its multiplicity constraints and explain explicitly the meaning or implication of those multiplicity constraints. (0.5 mark)

o Among all the weak entity types, explain for one of them why it is a weak entity type. If your current design doesn't have any weak entities, then explain which one of your current entities may be made a weak entity and why. (0.5 mark)

o Among all the generalisation/specialisation cases in the ER diagram, find a relatively nontrivial one in terms of the subclassing complexity, and explain the meaning of the actual subclassing in this case and the mearning of the associated participation and disjoint constraints. (1 mark)

o We note that, the design of this database can be much simpler or clearer if the staff's "availability" for work and the "venue" for the therapy in the mini case is not considered, i.e. all staff are always availabe, and all treatments are conducted on the same hall. Hence students may initially design their core database under this assumption, if they wish, before attempting the most complete design.

Mini Case: Beauty Salon

Beauty Salon is a system to be designed to manage the booking and the payment of a single beauty parlour.

Beauty Therapists: A beauty parlour has a number of staff members most of which are beauty therapists. Some beauty therapists are well-established professionals while others may just be the trainees. Different thereapists may also have different availability for work. For instance, some may not work on Saturdays while others might be on leave for a few weeks.

Beauty Care and Treatments: There are a number of beauty treatments or services a therapist can undertake, including manicure, pedicure, waxing, threading, facials and massages, to name a few. Different therapists may be able to provide different ranges of specific services, depending on the actual individuals. Some (item-based) services such as waxing will be charged per item while others (time-based) such as massage will be charged per half an hour or per hour. The fees for item-based services are fixed across all the therapists. But the fees for time-based services may vary among the therapists due to their different level of expertise.

Treatment Venues: When a booking is being made, it is possible to agree on a specific venue to conduct the services. Such venues could be other similar parlours or shops at which a particular beauty therapist also works.

Clients: Each regular client or patron will typically have her own client record set up on the system, and this will allow her to easily make a booking or make a payment. However, a non-regular customer will also be able to turn up in the beauty parlour and request a service to be done to her. Such customers don't have to create their client profiles and can still receive the services when they just turn up in the parlour, provided there are suitable therapists available at the parlour at the time.

Payments: The payment for the services of each booking or appointment will also be recorded. The payment can be made in cash, or via a credit card. The payment details will be recorded accordingly.

Business Activities: For the typical business activities, the Beauty Salon system will allow one to view which appointments have been made on any given day for any particular therapist, list all the available therapists for a given period of time, browse all the payments for a given day, and many more.

Solution

Title: Basic Database Design And Queries
Length: 3 pages (825 Words)
Style: MLA

Preview

Basic database design and queries (5 marks)

Q1. Basic database design and queries (5 marks)

A small shop wants to create a simple database to keep track of its merchandise prices, buyers and purchase orders. For simplicity, a merchandise will be recorded for its name and price, a buyer will be recorded for its name, and order will be recorded for its date and all the items included in the order.

i. Design a minimum database (a database of only essential attributes) to fulfil this purpose, and draw the ER diagram for your design. Indicate on the ER diagram the primary keys and the relationship multiplicities. By minimum we mean that you don't need to add anything that is not explicitly stated in the requirements, unless it's one of your artificially generated keys. For instance, you don't need to include a telephone number or email address for the entity corresponding to a buyer. (1 mark)

$7.00

Do you need an affordable high quality custom written paper?

Order a high quality custom written paper

With a team of proficient and enthusiastic writers, we are able to produce a compelling text on practically any topic.

Plagiarism Free

Excellent Quality

100% MONEYBACK