Ask DBMS Expert


Home >> DBMS

Database Concepts

Question: The Relational model

A spreadsheet was used to keep data for the booking system of the ABC Clinic, with sample data shown in Table 1. Columns 1-4 contain information for doctors, Columns 5-9 contain information for patients and the last 3 columns contain information for patient's appointments with doctors. The spreadsheet for the table is also available on Canvas.

1911_Data for the ABC Clinic.jpg

Table 1. Data for the ABC Clinic

With more and more patients and doctors, the ABC Clinic decides to go for database system to manage data. You are asked to design a relational database to keep data to answer clinic operation queries such as:
- List the patient appointments for each doctor for a given date.
- When a patient rings to make an appointment, give the available time slots for a given date.
- Retrieve the address of patients to send notices via mail services.

Questions:

A database schema of one relation as shown below is proposed, where semantics of attributes are self-explanatory. Discuss at least two weaknesses of this design.

ABC(doc-name, doc-gender, registration_num, qualification, pat-name, pat-gender, DOB, address, phone-num, appoint-date, appoint-time, type)

Propose your own design for the database. Your design must be able to keep all information shown in Table 1. Explain your design. Give the schema for each relation in your database and specify their data integrity constraints (underline primary keys and put asterisk for foreign keys).

Question 2. Relational model -- SQL DDL and DML

According to your design for the ABC database in Question 1.2, complete below tasks to populate your database with the data shown in Table 1.

Give the CREATE TABLE statement for each relation, including primary key and any foreign key constraints.

Give the INSERT INTO statements for each relation in your design. All data shown in Table 1 must be inserted into your database.

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 Oracle syntax and useful functions.

The ER model for the Academics database is as follows:

The relational schema for the Academics database is as follows:

321_Relational schema.jpg

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 (INTEREST) in several research fields.

Download and run the SQL script academics.sql on Canvas (the Oracle section) to define and populate the Academics database in your Oracle account.

Write ONE SQL query for each of questions 3.1--3.8, and each component of an SQL statement must be on a separate line. Your query should not produce duplicates in output but use DISTINCT only if necessary. Include answers for Questions 3.9 and 3.10 as comments starting with "//" to make an SQL script executable in SQL Developer.

Questions:

List the names of all the institutions (instname) currently in the database.
How many academics are there in the department where deptnum=100. Return the total number.
List in alphabetical order the family name (famname) and given name (givename) of academics who has a title.
List papers whose title contains the words "Data" or "Software", in upper or lower cases. List the
panum and title of these papers.
Return the panum of papers written by the academic "Mark Yee".
Return the famname and givename of academics whose acnum is in the range [100..199] or whose givename starts with "S".
List the panum of papers having at least two authors.
List in alphabetical order the famname, givename of academics who work for institutions in Queensland. Note: the value of Queensland in the database is QLD or Qld.
The below query is intended to list the fieldnum and title of fields whose fieldnum is between 500 and 599 or whose title contains the word 'Data'. But it has errors. Give the correct SQL query.
select fieldnum, title from field
where fieldnum >=500 and <=599 or upper(title) like 'Data%';
Describe in English what the output will be from the SQL query below. Do not give a literal description of each line of the query.
select acnum, field.fieldnum, title, descrip from field, interest
where field.fieldnum=interest.fieldnum and trim(ID) like 'B.1._';

Question 4. ER model

TXT is a logistics and transport company. You are asked to design a database for scheduling their transport jobs. Requirements for the database are as follows:
- Each truck has a unique registration No, and is also described by type, and year made.
- Each customer of TXT has a unique ABN and is also described by the name, and contact phone number.
- Delivery jobs are identified by the unique job No, and are also described by "from" and "to" destinations, and estimated distance to travel.
- Drivers are identified by the unique employee ID, and are also described by first name, last name, address and phone number.
- A customer may book in several delivery jobs and a job is from only one customer. A job must have an "owning" customer.
- A driver can be assigned several delivery jobs but a job has only one driver. Some description about the job is also recorded. Each job has a Status (eg open, complete,etc) and this is maintained in the database.
- A job is assigned only one truck but a truck can be assigned to several jobs.

According to the given description, state any assumptions you make and construct an Entity Relationship (ER) diagram for the database. You must represent entities, relationships and their attributes, and all applicable constraints in your ER diagram. Explain any concepts in the description that cannot 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 for this question.
- You can use the ER diagramming tool Dia, which can be downloaded from Canvas (The Tools 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.

Some common errors in ER diagrams:
- If the scenario is set in say, a club, you do not need an entity for the club. The arrangement you depict in the ER diagram is the club.
- Entities do not connect to other entities with straight lines. Entities have relationships to/with other entities so there has to be a relationship between connected entities.
- Every entity must have a primary key.
- The names of all relationships and entities must be unique.
- There are no foreign keys in ER diagrams. They come about as part of mapping an ER model into a relational schema.

Question 5. Relational database design.

Designing an ER diagram is the first step for database design. It is critical that we understand what makes a good design. Figure 2 is an ER diagram designed for the Orders database about customers and their orders for a company. The semantics of attributes, entity sets and relationships is as expressed in their names. Each item in the database must have an "owning" salesman, whether or not it is being ordered.

Discuss any issues with the given ER diagram. Give your suggested ER diagram.

Map your suggested ER diagram to a relational database schema. For each relation schema in the mapped relational database schema, indicate the primary key (underline) and any foreign keys (asterisk).

1868_ER diagram.jpg

Figure 2: The Orders database ER diagram

DBMS, Programming

  • Category:- DBMS
  • Reference No.:- M92773528
  • Price:- $45

Priced at Now at $45, Verified Solution

Have any Question?


Related Questions in DBMS

Data mining assignment -in this assignment you are asked to

Data Mining Assignment - In this assignment you are asked to explore the use of neural networks for classification and numeric prediction. You are also asked to carry out a data mining investigation on a real-world data ...

Sql query assignment -for this assignment you are to write

SQL Query Assignment - For this assignment you are to write your answers in a word document. This assignment is in three parts: Part A (reporting queries), Part B (query performance), Part C (query design). For this assi ...

The groceries datasetimagine 10000 receipts sitting on your

The groceries Dataset Imagine 10000 receipts sitting on your table. Each receipt represents a transaction with items that were purchased. The receipt is a representation of stuff that went into a customer's basket. That ...

You are in a real estate business renting apartments to

You are in a real estate business renting apartments to customers. Your job is to define an appropriate schema using SQL DDL in MySQL. The relations are Property(Id, Address, NumberOfUnits), Unit(ApartmentNumber, Propert ...

Objectivethe objective of this lab is to be familiar with a

OBJECTIVE: The objective of this lab is to be familiar with a process in big data modeling. You're required to produce three big data models using the MS PowerPoint software. This tool is available on UMUC Virtual Deskto ...

The relation memberstudentid organizationid roleid stores

The relation Member(StudentId, OrganizationId, RoleId) stores the membership information of student joining organization. For example, ('S1', 'O2', 'R3') indicates that student with Id 'S1' joined the organization with i ...

Relational database exerciseyou have been assigned to a new

Relational Database Exercise: You have been assigned to a new development team. A client is requesting a relational database system to manage their present store with the anticipation of adding more stores in the future. ...

Relational database design a given the following business

Relational Database Design A) Given the following business rules, identify entity types, attributes (at least two attributes for each entity, including the primary key) and relationships, and then draw an Entity-Relation ...

We can represent a data set as a collection of object nodes

We can represent a data set as a collection of object nodes and a collection of attribute nodes, where there is a link between each object and each attribute, and where the weight of that link is the value of the object ...

Data model development and implementationpurpose of the

Data model development and implementation Purpose of the assessment (with ULO Mapping) The purpose of this assignment is to develop data models and map Database System into a standard development environment to gain unde ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Why might a bank avoid the use of interest rate swaps even

Why might a bank avoid the use of interest rate swaps, even when the institution is exposed to significant interest rate

Describe the difference between zero coupon bonds and

Describe the difference between zero coupon bonds and coupon bonds. Under what conditions will a coupon bond sell at a p

Compute the present value of an annuity of 880 per year

Compute the present value of an annuity of $ 880 per year for 16 years, given a discount rate of 6 percent per annum. As

Compute the present value of an 1150 payment made in ten

Compute the present value of an $1,150 payment made in ten years when the discount rate is 12 percent. (Do not round int

Compute the present value of an annuity of 699 per year

Compute the present value of an annuity of $ 699 per year for 19 years, given a discount rate of 6 percent per annum. As