fb
$20 Bonus + 25% OFF
$20 Bonus + 25% OFF
Securing Higher Grades Costing Your Pocket? Book Your Assignment at The Lowest Price Now!
Add File

Error goes here

ISYS1055 Database Concepts

tag 0 Download3 Pages / 600 Words tag Add in library Click this icon and make it bookmark in your library to refer it later. GOT IT

Questions:

 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.
  1.  Give likely FDs.
  2. Give the candidate keys for the ABC relation. In your working, show how you develop the closure for each candidate key you have discovered..
  3. Is the relation ABC in BCNF or 3NF? Explain your answer.

2. Normalisation

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

Customer(custID, 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(custID, itemNo, orderID, quantity, discount, amount_due)

Given the FDs below:

orderID → custID, amount_due, discount

orderID, itemNo → quantity

custID, orderID → amount_due, discount

  1.  Give the minimal basis for the given FDs.
  2.  The Transaction relation is not in BCNF or 3NF. Give the reason.
  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.

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.

  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.
  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. Find papers that have three or more authors. Give the panum, title and number of authors for these papers.
  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.
  5. Give the total number of academics that do not have research interests. You must use the NOT IN operator.
  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.
  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.
  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.
  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 interestwhere upper(descrip) like '%DATA%';
  1. Consider the SQL query given 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

from Interest I1, Interest I2

where I1.acnum =AC1.acnum

and I2.acnum=AC2.acnum

and I1.fieldnum=I2.fieldnum);

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.

5. ER to relational schema mapping

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

  1. Give the FDs for the constraints in the ER diagram. You should not include trivial or redundant FDs.
  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.

Download Sample Now

Earn back the money you have spent on the downloaded sample by uploading a unique assignment/study material/research material you have. After we assess the authenticity of the uploaded content, you will get 100% money back in your wallet within 7 days.

Upload
Unique Document

Document
Under Evaluation

Get Money
into Your Wallet

Total 3 pages, 1 USD Per Page

Cite This Work

To export a reference to this article please select a referencing stye below:

My Assignment Help. (2020). Database Concepts. Retrieved from https://myassignmenthelp.com/free-samples/isys1055-database-concepts.

"Database Concepts." My Assignment Help, 2020, https://myassignmenthelp.com/free-samples/isys1055-database-concepts.

My Assignment Help (2020) Database Concepts [Online]. Available from: https://myassignmenthelp.com/free-samples/isys1055-database-concepts
[Accessed 23 October 2021].

My Assignment Help. 'Database Concepts' (My Assignment Help, 2020) <https://myassignmenthelp.com/free-samples/isys1055-database-concepts> accessed 23 October 2021.

My Assignment Help. Database Concepts [Internet]. My Assignment Help. 2020 [cited 23 October 2021]. Available from: https://myassignmenthelp.com/free-samples/isys1055-database-concepts.


Often times, students face problems with how to start a paper. Sometimes, they have no prior knowledge of paper writing conventions or how to place the arguments or cite facts and information. Despite being taught by teacher, many falter and in turn mess with their grades. Hire our experts to know how to write a research papers, create a research paper template paper outline and even draft the entire paper from a scratch. Certain things are not be experimented when grades are at stake and a research paper is offcourse not a thing to be tried like a novice. Get seasoned help from those who have helped students ace grades in research papers for past 10+ years.

Latest Database Samples

ITC566 Database System

Download : 0 | Page : 1

Answer:

ERD 1 (Task 1)

ERD 2 (Task 2)

ERD 3 (Task 3)

ERD 4 (Task 4) 

(TechTarget 2016)

(Cinergix Pty Ltd. 2011) 

References 

TechTarget (2016), entity relationship diagram, Retrieved from http://searchcrm.techtarget.com/definition/entity-relationship-diagram. 

Cinergix Pty Ltd. (2011), Ultimate Guide to ER Diagrams, Retrieved from http://creately.com/blog/diagrams/er-diagrams-tutorial/ 

Read More arrow

ITC516 Data Mining And Visualisation For Business Intelligence

Download : 0 | Page : 1

Answer: CCAvg Education Mortgage Personal Loan Securities Account CD Account Online CreditCard 1.6 1 0 0 1 0 0 0 1.5 1 0 0 1 0 0 0 1 1 0 0 0 0 0 0 1 2 0 0 0 0 0 1 1.5 2 0 0 0 0 1 0 0.6 2 104 0 0 0 1 0 8.9 3 0 1 0 0 0 0 2.4 3 0 0 0 0 0 0 2.5 2 0 0 0 0 1 0 1.5 3 0 0 0 0 1 1 0.9 2 111 0 0 0 1 0 2 ...

Read More arrow

ITC544 Computer Organisation And Architecture

Download : 0 | Pages : 2

Answers: 1: a). Single precision IEEE 754 format- 0 01111110 10100000000000000000000 The first MSB is the sign, the middle portion is the exponent and the last portion is the mantissa. Decimal conversion- 8.125 * 10^-1  b) 5-bit word- 0 1010 Signed magnitude- +10 (Most significant bit-0 [positive] rest 4 bits are magnitude) One’s complement- 10101 (all the 0’s are replaced by 1 and vice-versa) Two’s complement- ...

Read More arrow

CSE5DWD Data Warehouse Concepts And Design

Download : 0 | Pages : 7

Answer: Data Warehouse Bus Matrix: Business processes Dimensions Date Payment method Customer Employee Flights Membership Points Diagnosis Tool Products Agents Commission Flight management X     X X     X X X     Awarding programs and services X &nb...

Read More arrow Tags: Australia Truganina Computer Science Warehouse Concepts and Design University of Sydney Engineering 

ISYS1055 Database Concepts

Download : 0 | Pages : 9

Answer: Trivial Functional Dependencies: Table Name Attributes Dependencies Author Email Name, Address, Telephone1, Telephone2, Telephone3 Publisher Name Address, URL, ABN WrittenBy Email, ISBN Title Book ISBN Title, Edition, Year, ListPrice, PublisherName StokedAt ISBN, Code StockQty ShoppingCart CartID Timest...

Read More arrow Tags: Australia Deer Park Accounting Accounting theory and accountability University of New South Wales Managemnt 
Next

5% Cashback

On APP - grab it while it lasts!

Download app now

*Offer eligible for first 3 orders ordered through app!

ribbon
callback request mobile
Have any Query?