DATABASE IN INFORMATION TECHNOLOGY

QUESTION

Assessment details for ALL students
Assessment item 2—Assignment 2
Due date:
Friday of Week 10 (Midnight AEST time)
Weighting:
20%
Length:
Typically 10-20 hours
Objectives
This assessment item relates to the following course learning outcomes:
• database applications, systems, and the database environment
• database design using a specified data modelling approach
• normalisation theory
• relational database systems
• database implementation issues
• SQL
Important points
• Regular access to a system running an appropriate DBMS is required for this assignment.
Details of appropriate DBMS are provided later in this document.
• This assignment is an individual piece of work.  Discussing the assignment with other
students is encouraged; however, the document you submit must be an individual piece of
work, produced by you.  In your submission, identify any students who have influenced your
work on this assignment.
• The assignment due date is Friday Week 10.  The sample solution will be released on the
Monday following Week 12 (17 days after the due date).  A penalty of 5% of total marks
PER CALENDAR DAY will apply to late submissions.  Submissions made after release of
the sample solution will receive ZERO (0) marks.
• A late penalty can be avoided by applying for an extension.  AIC students must submit an
application to the local campus.  Other students must submit an application via the
electronic Assignment Extension System.  To avoid a late penalty, the application must be
submitted before the due date.
• An electronic submission is required for this assignment.  After making your submission,
download your submitted file to check it has not been corrupted.  Before submission, check
you have met all criteria in the marking sheet (available from the course website).
• This assignment is marked out of 40.  The mark you obtain contributes 20% towards your
final grade.
ASSESSMENT
2
Part A — SQL queries (8 marks)
This part of the assignment tests your understanding of SQL topics covered in Week 6.
1. Replace the query below with an equivalent query that is simpler, does not use UNION and does
use an OUTER JOIN: (2 marks)
SELECT Description,COUNT(NbrItemsRequested) AS TotalItems
FROM   Product AS p INNER JOIN SalesOrderProduct AS sop
ON p.PID = sop.PID
GROUP  BY Description
HAVING COUNT(NbrItemsRequested) >= 1
UNION
SELECT Description, 0
FROM   Product
WHERE  PID NOT IN (SELECT PID
FROM   SalesOrderProduct);

2. Formulate three queries to list the order Number of every “Placed” order that includes a request for
one or more Cookware items. (3 marks)
i. One query that uses IN twice and does not use EXISTS.
ii. One query that uses EXISTS once and IN once.
iii. One query that uses EXISTS twice and does not use IN.
3. In your own words, briefly describe the result produced by the query below. (1.5 marks)
SELECT Description
FROM   Product AS p
WHERE  NOT EXISTS (SELECT *
FROM   Customer AS c
WHERE  DiscountPercent < 10
AND  EXISTS (SELECT *
FROM   SalesOrder AS so
WHERE  so.CID = c.CID
AND  EXISTS (SELECT *
FROM   SalesOrderProduct AS sop
WHERE  sop.SOID = so.SOID
AND  sop.PID = p.PID)));
4. In your own words, briefly describe the result produced by the query below. (1.5 marks)
SELECT Description
FROM   Product AS p
WHERE  NOT EXISTS (SELECT *
FROM   Customer AS c
WHERE  DiscountPercent < 10
AND  NOT EXISTS (SELECT *
FROM   SalesOrder AS so
WHERE  so.CID = c.CID
AND  EXISTS (SELECT *
FROM   SalesOrderProduct AS sop
WHERE  sop.SOID = so.SOID
AND  sop.PID = p.PID)));
5. Advanced: Formulate an equivalent query to the one given in Question 4 above.  Your query must
not use EXISTS or IN.              (1 bonus mark)

Part B — ER model development (16 marks)
This part of the assignment asks you to develop an entity-relationship (ER) model of the data storage
requirements of a business (described in the case study below).  When preparing your ER model:
• It is recommended that you follow the modelling standard used in the Study Guide.
• If attributes that uniquely identify instances of an entity are mentioned in the case study, additional
attributes to uniquely identify entity instances should not be introduced.
• Some information in the case study may appear ambiguous; some required information may appear
to be missing.  Points of clarification about the case study can be obtained by email communication
with your local lecturer, local tutor, or the course coordinator.   Any points of clarification obtained
in this way must be clearly documented in your submission.
• An alternative to seeking points of clarification by email is to make reasonable assumptions about
missing or ambiguous information in the case study.  Marks will be lost for unreasonable
assumptions.  The course coordinator will determine if an assumption is reasonable.
The Case Study
CQUniversity have purchased an island in Keppel Bay, called CQUni-Island, which provides a cabin
style accommodation.  CQUniversity need your help in designing a database for CQUni-Island to
record details of bookings, the allocation of cabins to visitors, and items consumed during a visit.  The
data storage requirements are described below.
Each cabin is identified by a unique cabin number.  CQUni-Island offer a range of different types of
cabin – double, family, two-bedroom, etc.  The new database must hold a description of each cabin
and each cabin type, and the current charge rate for each type of cabin.
When a booking is made, the name of the guest making the booking will be recorded, along with an
email address and at least one phone number – a home phone number, work phone number, and
mobile phone number can all be recorded.  As part of the booking, the number and types of cabins
required must be recorded, along with the days over which each cabin is required.
For each booking, a full booking charge is calculated.  The operator may adjust this charge by
applying a percentage discount to the booking.  The charge rate for each cabin type required by a
booking must be recorded.  The full booking charge, discount percentage, final booking charge, and
booking fee (currently 10% of the final booking charge) will be recorded for the booking; a comment
can be recorded to explain the discount.
Note: The current charge rate for a cabin type will change over time.  However, the charge rate for a
required cabin type recorded in a booking is the one applied to the visit related to that booking.
When guests arrive at CQUni-Island, details of each guest will be recorded in the database.  Cabins
are normally allocated at check-in, but can be pre-allocated.  The name, contact phone numbers, email
address and postal address of each guest is recorded; the postal address is used if any personal
belongings are found after departure.  The cabin to which each guest is allocated must be recorded.
During a stay at CQUni-Island, guests may consume items from the fridge.  Any items consumed are
replaced each morning by the cleaner.  The date, type, price and number of each item consumed in a
cabin must be recorded in the database.  To ease data entry, each consumable item must also be
recorded in the database, along with the current price of that item.
On departure, a final charge is calculated for the visit.  This value is calculated from the cabins used
and items consumed.  A discount percentage may be applied to cabin charges; this can be taken from
the booking details.  A charge rate must be recorded for each cabin used; again, this can be taken from
the booking details.  The full cabin charges, discount percentage, final cabin charges, total
consumables and final charge must all be recorded for the visit.
Payments are handled by an existing accounting system.  This is out of scope for the new database
application.
Part C — Table design (8 marks)
Two years ago CQUni-Island (see Part B case study) started recording bookings in a Microsoft
Access table called BookingCabinRequirement, shown below with sample data.  Unfortunately, some
anomalies have arisen while using this table.
The BookingCabinRequirement table has been used to perform many roles for CQUni-Island,
including:
• check the number of cabins booked over a given range of days
• obtain the phone number of guests affected by a change in cabin availability
• check the discount percentage for a booking
The table has a composite primary key of BookingID, CabinType, FirstNight and LastNight.

BookingCabinRequirement
Booking
ID
Guest
ID
Discount
Percent
Final
Booking
Charge
Cabin
Type
Nbr
Required
First
Night
Last
Night
Charge
Rate
Guest
Name
1 1 10 756 Double 2 06/05/11 08/05/11 90 Joe Jones 0400111111
1
1 10 756 Family 1
06/05/11
08/05/11 100 Joe Jones 0400111111
2 2 5 285 Family 1
06/05/11 08/05/11
100 Hong Lee 0400222222
3 3 5 760 Family 1
06/05/11 08/05/11
100 Raj Prasad 0400333333
3 3 5 760 Family 1
06/05/11 10/05/11
100 Raj Prasad 0400333333
4 1 10 243 Double 1
13/05/11
15/05/11 90 Joe Jones 0400111111
Explanation of sample data
Booking 1
This booking is recorded in two rows.  Two cabins of type Double are required from the 6
of May to
the 8
th
of May – a total of three nights (2x3x$90=$540).  A cabin of type Family is required over the
same days (1x3x$100=$300).  A 10% discount is applied, giving a final booking charge of $756.
Booking 3
This booking is recorded in two rows.  One cabin of type Family is required from the 6
of May to the
8
th
of May.  A second cabin of type Family is required from the 6
th
May to the 10
th
May.  A 5%
discount is applied, giving a final booking charge of $760.

Questions
Support your answers to questions below with any assumptions that help to clarify your understanding
of data in the BookingCabinRequirement table.
1. Identify one anomaly that might arise from using the BookingCabinRequirement table.  (1.5 mark)
2. Identify all irreducible functional dependencies in table BookingCabinRequirement.   (2.5 marks)
3. Explain why table BookingCabinRequirement does not satisfy BCNF.   (1.5 mark)
4. Convert table BookingCabinRequirement into a set of BCNF tables that can hold all of the data
that can be held in table BookingCabinRequirement.  (2.5 marks)
Note:  Document the tables in your design using the notation below.
Customer(CID, Name,  Street, Suburb, State, PostCode, DiscountPercent, Email)
SalesOrder(SOID
, CID,  Number, FullPrice, Discount, FinalPrice, TotalPaid, Status)
foreign key (CID) references Customer(CID)
th
th
Guest
Phone
Nbr
Part D — Data Model Transformation (8 marks)
Implement the ER model you developed in Part B as a relational database design.
1. Transform your ER model into a relational database design.  (2 marks)
Note:  Document the tables in your design using the notation shown in the example below.
Customer(CID, Name,  Street, Suburb, State, PostCode, DiscountPercent, Email)
SalesOrder(SOID
, CID,  Number, FullPrice, Discount, FinalPrice, TotalPaid, Status)
foreign key (CID) references Customer(CID)
2. Implement sufficient tables to support the requirements below.  You do not need to implement
(create) the entire database.  You just need to implement enough of the database to demonstrate
(and document) the following:
• use of data types: provide examples of four different data types used in your design
• primary key constraints: provide two examples
• foreign key constraints: provide two examples
• a unique constraint: provide one example
• a cascading delete: provide one example
• column constraints (a constraint that affects only one column): provide two examples
• a table constraint (a constraint that affects more than one column): provide one example
• a default value: provide two examples

Which DBMS can be used for this activity?
You may use Microsoft Access (version 2000 or later), SQL Server (version 2005 or later) or My SQL
(version 5 or later) for this activity.
How to submit your database implementation?
• If you are submitting a Microsoft Access implementation of your design, you must submit a
database file (Ass2.mdb or Ass2.accdb).
• If you are submitting a SQL Server or MySQL implementation of your design, you must
submit a script that can be run to create your tables.
Note: You need to include brief documentation (in a text file or word document or in your script file)
that documents and highlights where the above items are demonstrated in your database.  e.g. where
are the primary key constraints.  Marks will be lost if your documentation is missing or not clear or concise.
What you must submit
You must make an electronic submission for this assignment.  Your submission must consist of a
single ZIP file.  Your ZIP should contain two files: (1) a Microsoft Word document and (2) a
Microsoft Access database file (Ass2.mdb or Ass2.accdb) or database script (Ass2.sql).
Your Microsoft Word document should contain:
• your answers to questions in Part A
• your ER diagram for Part B
• your reasonable assumptions for Part B (if you have any)
• documentation for points of clarification obtained for Part B (if you have any)
• your answers to questions in Part C
• your relational database design for Part D
• documentation describing the required features of your database implementation for Part D
If using Microsoft Access, your database should contain:
• the tables you have implemented to support your documentation for Part D
If using SQL Server or MySQL, your database script should contain:
• create table statements to support your documentation for Part D

SOLUTION

KI86

Please  Click on the  below links to Chat Now  or fill the Order Form !