Design a database schema

Assignment Help Database Management System
Reference no: EM132012846

Assignment

Learning Objectives Assessed:
CLO 1: Describe various data modelling and database system technologies.
CLO 2: Explain the main concepts for data modelling and characteristics of database systems.
CLO 3: Identify issues with and compare, justify relational database design using the functional dependency concepts.
CLO 4: Apply SQL as a programming language to define database schemas and update database contents.
CLO 5: Apply SQL as programming language to extract data from databases for specific users' Information needs
CLO 6: Design a database schema using conceptual modeling mechanisms such as entity-relationship diagrams.

Question 1. The Relational model

A database is needed to keep data for the booking systems of the ABC Clinic. Consider the below database schema of one relation including
attributes for doctors (doc-), patients (pat-) and appointments (app-). ABC(doc-firstname, doc-surname, doc-gender, doc-rego, doc-qualification, pat-ID, pat-givename, pat-surname, pat-gender, pat-DOB, pat-addr, pat- phone, app-ID, app-datetime, app-type)

- A doctor has a unique registration number (doc-rego) and is also described by name, gender and qualification.
- A patient is identified by a unique patient ID (pat-ID) and has other information.
- Each appointment by a patient with a doctor is assigned a unique appointment ID (app-ID). An appointment can be of the long or short type.

Answer questions:
1.1) Give likely FDs.
1.2) Give the candidate keys for the ABC relation. In your working, show how you develop the closure for each
1.3) Is the relation ABC in BCNF or 3NF? Explain your answer.

Question 2. Normalisation.

Consider the two relations below. They are in BCNF with primary key attributes underlined:

Customer(custlD, firstname, lastname)
Item(itemNo, desc, price)

A Transaction relation as below is proposed to keep data for orders. Each order is by one customer and it can contain multiple items with
their quantities.

Transaction(custlD, itemNo, orderlD, quantity, discount, amount_due)

Given the FDs below:
orderlD custlD, amount_due, discount
orderlD, itemNo . quantity
custlD, orderlD amount_due, discount

Answer questions.
2.1) Give the minimal basis for the given FDs.

2.2) The Transaction relation is not in BCNF or 3NF. Give the reason.

2.3) Follow the BCNF/3NF decomposition algorithm to decompose Transaction into relations in BCNF or 3NF.

Give the relations after decomposition and specify the primary key and any foreign keys for each relation.

Question 3. SQL.

In addition to the lecture notes, you should also study by yourself the SQL*Plus tutorial on Canvas (the Oracle section) and other resources for syntax and useful functions.

The relational schema for the Academics database is as follows:
DEPARTMENT(deptnum, descrip,
instname, deptname, state, postcode)
ACADEMIC(acnum, deptnum*, famname,
givename, initials, title)
PAPER(panum, title)
AUTHOR(panum*, acnum*)
FIELD(fieldnum, id, title)
INTEREST(fieldnum*, acnum*, descrip)

Some notes on the Academics database:

- An academic department belongs to one institution (instname) and often has many academics. An academic only works for one department.

- Research papers (PAPER) are often authored by several academics, and of course an academic often writes several papers (AUTHOR).

- A research field (FIELD) often attracts many academics and an academic can have interest in several research fields (INTEREST).

Primary keys are underlined and foreign keys are marked with *. You should download the SQL script for defining and populating the database academics.sql from Canvas (the Oracle section) and run academics.sql in your Oracle account to build the database.

Write ONE SQL query for each of questions 3.1)-3.9). Put your answer for Question 3.10) in comments (starting each line with "--").

Notes for marking:
- Each question is worth 4 marks. For questions with the "You must (not) requirement, queries failing to meet the requirement receive maximum 1 mark. For example, question 3.1) has "You must use the NATURAL JOIN operator". A query not using the NATURAL JOIN operator receives maximum 1 mark.

- Do not include the result of the query or the script used to create the tables.

- Your query should not output duplicates but use DISTINCT only if necessary.

- Queries are marked in terms of both correctness and efficiency. Unnecessary joins will incur deduction.

3.1) List the deptnum and total number of academics for CS departments, in alphabetical order of deptname. CS departments are departments whose deptname contains the phrase "Computer ... Science" or "Computing Science" in upper case or lower case letters. You must use the NATURAL JOIN operator.

3.2) List research fields where at least one academic is interested in. List the fieldnum, ID and title of these research fields. You must use a
subquery. 

3.3) Find papers that have three or more authors. Give the panum, title and number of authors for these papers.

3.4) For EACH academic, compute the total number of papers s/he has written. Output should include the acnum and total number of papers for each academic. In particular, an academic without any papers should have zero(0) as number of papers in the output. You must use a JOIN operator.

3.5) Give the total number of academics that do not have research interests. You must use the NOT IN operator.

3.6) Are there any research fields where less than 20, including zero, academics are interested in. List the fieldnum, ID, title and number of
interested academics for these research fields.

3.7) Find the papers whose title contain the string 'data' and where at least one author is from the department with deptnum 100. List the panum and title of these papers. You must use the EXISTS operator. Ensure your query is case- insensitive.

3.8) Return the research interest that has the largest number of interested academics. You must not use MAX. Note: An SQL query that lists all research interests in decreasing order of their total number of interested academics is incorrect.

3.9) The following SQL query is intended to find academics (acnum) who are ONLY interested in "Data" (descrip) fields. But it is incorrect. Give the correct SQL query.
select acnum
from interest
where upper(descrip) like '%DATA%';

3.10) Consider the SQL querygiven below, give the English explanation for the output of a) the subquery, and b) the whole SQL query. Literal explanation will receive zero mark.

select distinct AC1.givename, AC1.famname,
AC2.givename, AC2.famname
from academic AC1, author AU1, academic
AC2, author AU2
where AC1.acnum=AU1.acnum
and AC2.acnum=AU2.acnum
and AU1.panum=AU2.panum
and AU2.acnum>AU1.acnum
and not exists
(select *
from Interest |1, Interest |2
where |1.acnum =AC1.acnum
and |2.acnum=AC2.acnum
and |1.fieldnum=12.fieldnum);

Question 4. ER model.

The A-Star consulting firm has decided to build a database. The company has hired you to design the database. Requirements are as follows.

- Clients are given a unique client-ID. Other client details including name, address and telephone number are also recorded.

- A-Star has several departments. Each department is described by a unique name, address and contact phone number. Each department must have a manager and has many employees. A manager can only manage one department.

- Each employee of A-Star has a unique employee ID, name, and birth date. An employee must belong to a department.

- An employee is assigned as the consultant for a group of clients. But a client has only one consultant.

- Once signed up, clients book consultation sessions with their consultant. All consultation sessions for a client are numbered sequentially as 1, 2, 3, etc. and the details for consultation sessions are also recorded, including date, time and topic.

- A-Star has vehicles for visiting clients. Each vehicle has a registration number, model, and year the vehicle was made. Consultants book
vehicles for consultation sessions with clients.

The database is aimed to:

- Keep track of data for departments, employees, vehicles and clients.

- Keep track of the consultation sessions for clients and vehicle usage for booking vehicles for consultation.

- Keep track of scheduled consultation sessions for consultants.

According to the requirements and design aim, give an Entity Relationship (ER) diagram for the database, making assumptions where necessary.

You must represent entities, relationships and their attributes, and all applicable constraints in your ER diagram. Explain any constraints that can not be expressed in the ER diagram.

- Your ER diagram must only use notations from the lecture notes and must not be hand drawn. ER diagrams using other notations will receive zero mark.

- You can use the ER diagramming tool Dia, which can be downloaded from Canvas (The ER section) and is also available as an App on mydesktop.rmit.edu.au. When exporting your ER diagram in Dia to a pdf file, you need to first set "page setup" to "Fit to 1 by 1" so that your pdf diagram scales properly. You can also use any other diagramming tool.

Question 5. ER to relational schema mapping.

Consider the Musician database ER diagram as shown in Figure 1.

5.1) Give the FDs for the constraints in the ER diagram. You should not include trivial or redundant FDs.

5.2) Map the ER diagram to a relational database schema following the ER- to-relational-database-schema mapping rules. Indicate the primary key (underline) and any foreign keys (asterisk) in each relation.

2219_The Musician database ER diagram.jpg

Fig. 1 The Musician database ER diagram

Verified Expert

The solution file Is prepared in ms word which consists of Design relational database for ABC case study which includes functional dependency and bcnf. We did normalization for customer database and defined functional dependency and identified 3nf. Also identified decompose algorithm to decompose transaction. Executed required queries on academic database and design er model a star database. The last question converted er model to relational scheme.

Reference no: EM132012846

Questions Cloud

Compare the risks of the two options : What kinds of risk do you face for option 1 and options 2? Compare the risks of the two options.
Determine the equilibrium forward exchange rate : Suppose the current spot exchange rate is what you calculated in part a, what is the equilibrium forward exchange rate?
Fda food safety modernization act : She also notices raw chicken thawing directly above sliced tomatoes and prewashed salad greens. Identify the food safety hazard(s)?
What is the amount of each withdrawal : How much is in the account after the last deposit is made? How much was deposited?
Design a database schema : Describe various data modelling and database system technologies - Explain the main concepts for data modelling and characteristics of database systems
Accident the periosteum and the muscle : Predict what would happen if during an accident the periosteum and the muscle were removed from a side of a long bone that is broken.
How much money will you have after 8 years : suppose you invgest $4,500 today in an account that earns a nominal annual rate(i nom) of 16 percent with interest compounded semiannually.
What is covariance between the portfolio and market index : What is the standard deviation of the portfolio? What is the beta of your portfolio? What is firm specific variance of your portfolio?
Calculate the standard deviation of the returns : Std Deviation of a portfolio having a stock and a risk free security = Ws*SDs = 0.50 * 20% = 10% ## Ws = Weight of security SDs = Std deviation of security

Reviews

len2012846

6/8/2018 7:25:35 AM

Weight Criteria Details 20% Application of the functional dependency theory for database design 40% Application of SQL for complex querying 20% Design databases using the ER model Mapping an ER model into relational tables

len2012846

6/8/2018 7:25:27 AM

• Enabling Knowledge: You will gain skills as you apply data modelling knowledge effectively in diverse contexts. • Critical Analysis: Analyse and model requirements and constraints for the purpose of designing and Implementing software artefacts and IT systems. • Problem solving: Design and implement database solutions that accommodate specified requirements and constraints, based on analysis or modelling or requirements specification.

len2012846

6/8/2018 7:25:11 AM

You are welcome to bring your questions on Assignment 2 to the Assignment 2 hotdesk consultation sessions in Week 11: Name Time Date Location Jenny 16:00-17:00 Mayday 14 14.09.05 ian.baker 19:30-20:30 Tuesday 15 May 14.11.16 xiaoling.dal 10.30 - 11.30 Friday 18 May 12.08.02 lei.zheng 18:30-19:30 Wnesday 16 Mayed 080.10.016 Shaahin Madani 11:30 - 12:30 Thursday 17 May 80.05.11 vuhuy.mal 15:30 - 16:30 T uesday 15 May 14.11.16 Adrian Luke 10:30-11:30 Mu Tes day 15 M ay 14.09.13 Praveen.Koirala 18:30-19:30 WMaednesday 16 May 056.05.094

Write a Review

Database Management System Questions & Answers

  Knowledge and data warehousing

Design a dimensional model for analysing Purchases for Adventure Works Cycles and implement it as cubes using SQL Server Analysis Services. The AdventureWorks OLTP sample database is the data source for you BI analysis.

  Design a database schema

Design a Database schema

  Entity-relationship diagram

Create an entity-relationship diagram and design accompanying table layout using sound relational modeling practices and concepts.

  Implement a database of courses and students for a school

Implement a database of courses and students for a school.

  Prepare the e-r diagram for the movie database

Energy in the home, personal energy use and home energy efficiency and Efficient use of ‘waste' heat and renewable heat sources

  Design relation schemas for the entire database

Design relation schemas for the entire database.

  Prepare the relational schema for database

Prepare the relational schema for database

  Data modeling and normalization

Data Modeling and Normalization

  Use cases perform a requirements analysis for the case study

Use Cases Perform a requirements analysis for the Case Study

  Knowledge and data warehousing

Knowledge and Data Warehousing

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Practice on topic of normalization

Practice on topic of Normalization

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd