Discuss likely data integrity constraints on attributes

Assignment Help Database Management System
Reference no: EM13708320

Question 1: The Relational model.

Consider the relations Book and Author below with some sample data:

Semantics of attributes is self-explanatory. Some additional notes are as follows:

• isbn is a unique number that is assigned to each book that is published.

• A book may have one or more authors and an author can write more than one book.

• A book has only one publisher, and a publisher has an address (pubAddr). Each author has some description (authorBio).

Each question below is worth 6 marks.

a) Discuss likely data integrity constraints on attributes and specify all candidate keys and the primary key for each relation based on these constraints.

b) Specify any foreign keys for each relation and explain the primary key that the foreign keys reference.

c) Give the schema for each relation, and underline the primary key for each relation schema.

d) Based on the schema for relation Author, would the following INSERT statement

INSERT INTO BOOK VALUES(104, NULL, ‘RMIT Professor') execute successfully? Explain your answer using data integrity constraints.

e) (research question) Discuss any issues you see in the given relation schemas under the data integrity constraints. Hint: Consider updates to relation instances.

Question 2. ER model.

A small bus company wants a simple database to track some aspects of its operation. They describe key elements of their requirements in the following points . Use the following information to understand their requirements and draw an ER diagram.

• The company has buses that can be identified by their registration number and it is important to record the number of seats, capacity and the last service date.

• Bus routes are known by their Route Number and all route numbers start with the capital letter "R". All routes have a start terminus and an end terminus and the distance of the route.

• The driver's details are name, their start date with the company, their licence number and its expiry date.

• Each bus driver is allocated a bus and only that driver will drive that bus. However, at any given time, some drivers will be on leave and some buses will be in for servicing.

When this happens, they are not removed from the database; they are just left un-assigned.

• A bus is allocated to just one route.

• For OH&S reasons, the company runs different shifts (Morning, Afternoon, Night). To cover all the shifts, multiple buses are assigned to each route. A given bus on a particular route is allocated a shift.

• Several mechanics work for the company. Their name and unique certificate number are required in the database. When a bus is being serviced, several machanics will work on it at the same time to get the job done faster. To get maximum efficiency from the mechanics, each mechanic has two or more jobs on the go at any one time.

According to the given description, construct an Entity Relationship (ER) diagram for the database, and make assumptions where necessary. You must represent entities, relationships and their attributes, and all applicable constraints in your ER diagram.

Explain any concepts in the description that can not be expressed in the ER diagram.

Your ER diagram must use notations in the lecture notes and should not be hand drawn. ER diagrams using other notations will receive zero mark for this question. You should use the ER diagramming tool Dia, which can be downloaded from the course Blackboard ("Database and Tools ! ER Diagram Tools").

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 the mapping process into a relational schema.

Question 3. SQL

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 write several papers (AUTHOR).

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

Primary keys are underlined and foreign keys are marked with *. The SQL script for defining and populating the database academics.sql can be downloaded from the course Blackboard ("Databases and Tools ! Databases").

Write one SQL query for each question below; otherwise you will receive zero for the question. Each component of an SQL statement must be on a separate line. For example, SELECT * FROM Department WHERE State='VIC';

Do not include the result of the query or the script used to create the tables. Your query should not produce duplicates in output but use DISTINCT only if necessary.

In addition to the lecture notes, you need to study by yourself the SQL*Plus tutorial on the course Blackboard ("Using Oracle ! SQL*Plus tutorial") for Oracle syntax and useful functions.

a) List the first name and last name of professors in the University of Melbourne in the database. Output should be sorted in the alphabetical order of last name and then first name. Note: The title of professors may be recorded as "Professor" or "Prof" in the database and can have upper or lower case letters.

b) In how many institutions (identified by instname) are there Computer Science-related departments? Return the number. Note: Computer Science-related departments are departments whose name contains the word "Computer" or "Computing" in upper or lower case letters.

c) Name the field number (fieldnum) and title for research fields whose title contains 'data'. Output should be in the decreasing order of fieldnum. Note that 'data' may be in upper or lower case letters.

d) How many departments do not have the state information? Return the number.

e) List the deptnum, instname and state for institutions whose departments do have the state information. Output should be in alphabetical order of states and then increasing order of deptnum.

Question 4.

a) Give the CREATE TABLE statements to define the Book and Author relations in Question 1, including the primary key and any foreign key definitions.

b) Give the INSERT statements to insert the given sample data into the Book and Author relations.

c) Consider Question 1(e). Propose a different design for relation schemas to keep data for books and authors. Discuss the pros and cons of your design in comparison with the given relation schemas.

Questions below are regarding the Academics database.

d) Write an SQL query to find out how many Computer Science departments are not in Victoria. Return the number.

e) Write a sequence of at most two SQL queries to produce the list of first name and last name for staff in the department "RMIT CS" (department description). The list should be in the alphabetical order of last name and then first name.

Reference no: EM13708320

Questions Cloud

Ourselves internally about persistence and patience : First, there are stories we tell ourselves internally about persistence and patience, long-term thinking, staying focused on the customer. Second, we select people who, when they wake up in the morning, are thinking about how to invent on beha..
How does television impact the men and women : Watch the premier episode of "The Honeymooners" "TV or not TV," which aired in 1955 -  How does this text express some of the contradictions regarding television and domesticity that Spigel describes?
Internal operations using best practices : Research organizations that have improved their internal operations using best practices including tools discussed in this module.Select an organization that you believe has successfully improved its strategy execution, and research it to jus..
Review porter''s five-forces model of industry competition : Review Porter's Five-Forces model of industry competition. Using your own words (do not copy definitions from the text), explain the model's importance in the concept of strategic grouping within industries. Then, find a company with a "vision..
Discuss likely data integrity constraints on attributes : Write an SQL query to find out how many Computer Science departments are not in Victoria. Return the number -  Discuss likely data integrity constraints on attributes and specify all candidate keys and the primary key for each relation based on the..
The health care organization accreditation : The mandate for improving the way in which health care is delivered was stimulated by the public outcry over the estimated 98,000 deaths because of medical errors each year, according to the Institute of Medicine in 1999. Since then, health car..
Case studies are used extensively in teaching business : Case studies are used extensively in teaching business. Typically, students perform an analysis on a case study prepared by an author or the professor-in this instance, the student is the author of the cases. The case study research strategy pr..
Management issues discuss management issues : Management issues Discuss management issues that arise when businesses utilize the Internet as an information and communications tool. Include service and speed issues as well as privacy, fraud, security, and unauthorized Internet sites in you..
First find the slope or gradient : First find the Slope or Gradient - What is the equation of the straight line shown in the diagram.

Reviews

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