Advanced Databases

The  coursework  must  be  submitted  on  the  module  Moodle  page  by  the deadline
specified, into the Resit submission area.

The coursework is divided in three parts: Part I, Part II (consists of two sub-sections)
and Part 3.

Part I

Consider  the description  of  the  requirements  for a  database system  of  a  restaurant
chain named EatWell.

The restaurant chain contains different branches that are identified by a unique code,
location, size (number of seats), and contact phone number. The restaurant chain has
different  types  of  employees  that  work  in  the  chain  like  waiters,  chefs  and  cleaners,
Each  employee  is  identified  by  a  code,  has  a  name,  home  address,  phone  number,
age,  and  sex.  The  restaurant  chain  requires  the  chef  to  be  qualified  and the
information  about  the institution where  the  chef  has  graduated  from  is  kept  in  the
database. The system also needs to keep information about the working shifts for each
waiter and cleaner.
The  chain  contains  different  types  of  menu  that  are  identified  by  a  code,  type  and
name of the creator. Each menu is composed of various dishes and a dish can be in
various menu types. The dishes are described by a code, name, description, and price.
Each  branch  can  serve  different  menus  during  the  day  and  the  same  menu  can  be
served  in  different  branches.  Depending  on  the  menu  served  in  each  branch,  the
necessary  items  (ingredients  and  material)  are ordered.  Each  item  is  identified  by  a
number  and  has  a  description.  The  dates  in  which  the  items are  ordered  and  the
quantity are kept in the database.
For each branch, the system needs to monitor information about the branch’s income
per day. An income is identified by a number, has a date, and total amount.

Create an ER-diagram  for the  above database. Your diagram should specify  entities,
relationships, attributes, primary keys and cardinalities; the following should be also
specified  (if  applicable):  aggregate  entities, participation  constraints  and  existential
dependencies.

Part I carries 20% of the mark.
Part II
Part II – Section 1 – Oracle ORDBMS schema
Based  on  the  requirements  of  the  system  in  Part  I  and  the  ER diagram  you  created,
provide  an  object-relational  database  schema  (ORDBS)  using  Oracle  SQL3  for  the
EatWell system.
Please  note  that  all  date  values ought  to  be in  the  format  of  DDMMMYY  (e.g.,
25DEC11), and all time values are in the format of HH:MM (e.g., 22:10)

Part II, Section 1 carries 35% of the mark.
Part II – Section 2 – Oracle ORDBMS SQL3 queries

a)  List the names of the employees of the branch with code B111.
[10%]
b)  List  the  code  and  location  of  the  branches  that  have an  income  of  at  least  5,000
GBP on the 10/APR/2014.
[10%]
Part II, Section 2 carries 20% of the mark.
Part III – Answer the following questions:

a)  Does  Oracle  implementation  of  SQL3  allow  for  multiple  or  single  inheritance?
What is the keyword used for specifying type inheritance in Oracle SQL3?
[7%]
b)  Consider  that  a  site  participates  in  a  2-Phase  Commit  (2PC)  protocol.  Consider
that it has successfully executed the local transaction and voted for commit to the
coordinator. Can it at this moment abort the transaction (by writing a <abort T>
log record)? Can it at this moment commit the transaction (by writing a <commit
T> log record)? Justify your answer.
[8%]
c)  Consider  the  DTD and  XML  documents  below.  These  documents  have several
different  errors,  which  violate  well-formedness  and  validity  of  the  XML
document. Identify these syntax errors and explain them briefly in your answer.
[10%]
Part III carries 25% of the mark.

 PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET AN AMAZING DISCOUNT 🙂