Reference no: EM132505778

ISYS1055 Database Concepts - RMIT University

1. Overview
Database systems are a key technology for the storage, management, manipulation, and retrieval of structured data. They have an impact on the use of information technology in applications ranging from banking, to travel bookings, to online shopping. In this assignment you will apply the skills and concepts that you have learned about database systems in the course so far.

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

Problem 1. 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 1.3 through to 1.15. Your query must be formatted in such a way that it could be directly ported and run in Oracle SQL Developer. For example, if you use explanatory comments, they must be appropriately formatted SQL comments. Do not include the output of the query or the script used to create the tables.

Explain the following query in English. A literal explanation will receive 0 marks.

select givename, famname, instname from academic natural join department where acnum in
(select acnum from author
where acnum not in
(select acnum from interest group by acnum))
and deptNum in
(select deptNum from academic
where deptname = ‘Computer Science');

The following SQL query is meant to output a list of papers (panum) with the total number of authors for each paper. It has syntax errors and logic errors. Explain the syntax and logic errors and give the correct query.

select PaNum, count(A1.AcNum) from Author A1, Author A2 where PaNum = A2.PaNum group by PaNum;

Find departments that have a description (descrip) available in the database. Return all details of these departments.

List the paper number and title of papers by the academic whose acnum is 100.

For each academic, give the acnum, givename, famname and the total number of papers s/he has written. Note that if an academic has not written any paper, his/her total should be zero. You can use JOIN operators such as NATURAL, JOIN ...ON.

The research field ID is a research field classification code representing classes for three "Levels". These three Levels are separated by a "full stop" in a single string. For example the research field ID "B.1.6" represents that the research field belongs to Class "B" for Level one, Class "1" for Level two and Class "6" for Level three. For research fields in Class "1" for Level two, list the field IDs and the number of academics for each field ID.

Find departments where at least one academic does not have research interest, and list the deptnum, depntname, instname of these departments. Must use a subquery.

Output in alphabetical order the acnum, famname, givename, and department number (deptnum), and description (descrip) of authors whose family name starts with "C".

List the fieldnum, title, and total number of interested academics (under the heading "NO. ACADEMICS INTERESTED") in each research field, in increasing order (i.e. ascending order) of fieldnum.

List in alphabetical order the institution and name of departments where at least 10 academics have written papers.

List the deptnum of departments whose postcodes are in the range 3000..3999 and that do not have any academics with the title of Professor (stored as "Prof" or "Prof." in the database) , including departments that do not have any academics.

Find the departments that have produced at least ten papers (that is, those departments where the sum of papers written by their academics is at least ten). Output their deptnum and deptname in ascending order.

List the deptnum and deptname of departments whose academics have never written any papers.

1.14 List papers (panum) by academics with research interests in fields related to "data". You must use EXISTS. Note that "fields related to data" includes any occurrence of the four letters "data" within a field name, in any case.

1.15. The popularity of a field is measured by the number of interested academics. List details (filednum, ID and title) of the most popular field together with the total number of interested academics.

Problem 2. The Relational Model.

Consider the below relational database schema for a project management database of five relations. A sample tuple is given for each relation to help explain the meaning of attributes.

The Project Management Database
Department(deptID, deptName, manager)	<2, 'Production', 'E5'>
Employee(empID, empName, deptID, email)	<'E4', 'Ann', 4
Project(projID, startYear, deptID)	<'P3', 2000, 2>
EmpProj(empID, projID, role)	<'E2', 'P6', 'Designer'>
Evaluation(projID, manager, evalDate, grade)	<'P3', 'E5', '11-01-2020', 5>

Assume that
• Each employee belongs to one department. Each department has one manager.
• Each project is for one department only.
• An employee can work for many projects with different roles, but has one role for a project. A project can have many employees.
• The progress of projects is evaluated once a week by department managers. The evaluation of a project on a particular date has a grade, which is an integer between 0 (very bad) to 5 (excellent).

Answer the following questions based on the given description.

Give all likely FDs. Do not include trivial or redundant FDs.

Give {empID, projID}+ and {deptID}+ based on the FDs in Problem 2.1.

Specify the primary key (by underlining) and any foreign keys (with *) for each relation. 2.4: (2 points) Discuss the normal form for the Evaluation relation using the FDs in Problem 2.1.

Problem 3. Normalisation

Consider the following APP relation schema about patients and their scheduled appointments with doctors at a clinic:
APP(docID, docName, patID, patName, patDOB, appDate, appTime, roomNo)

The semantics of attributes are as follows:
• Several doctors are on duty to see patients at the clinic. docID and docName are the unique ID and name of doctors.
• Attributes patID, patName and patDOB are the unique ID, name and date of birth of patients. Attributes appDate and appTime are the date and time for a patient's appointment.
• The clinic has several consultation rooms. A doctor is assigned a particular room on a day for consultation, and no two doctors are assigned the same room on the same day.
• A patient can make an appointment for a particular date and time for consultation with a doctor. A patient can only make one appointment for a day.

FDs based on business rules are given as follows: docID --> docName
patID --> patName, patDOB
patID, appDate, appTime --> docID, roomNo appDate, docID --> roomNo
patID, appDate --> appTime, roomNo, docID Answer the following questions.
The given FDs may have redundancies. Give the minimal basis for the given FDs.

Discuss all candidate keys for the APP relation. Explain your answer using the functional dependencies in Problem 3.1.

The APP relation is not in BCNF or 3NF. Decompose the relation into BCNF/3NF relations. Your decomposition must keep all functional dependencies and must be lossless. For each resultant relation, discuss if it is in BCNF or 3NF and indicate the primary key (underline) and any foreign keys (*).

Problem 4. ER Model

Due to your experience in designing the database for the "Legendary League" game, you have been asked to design the ER diagram for a bigger database to manage the events for the "Legendary League" eSports Oceanic Championship (OC). The requirements are as follows:

• Registered teams compete in the OC. Each team has a name, and a number of team members. A team also maintains a rank throughout the OC, reflecting how well it is doing in the championship.
• Team members are identified by a unique member id, their first name, and their surname. There are two types of team members: managers, and players. For each manager, their number of years of experience is tracked. Each player in a team has an in-game player name, and an assigned role in the team. A role can be one of "top", "mid", "jungle", "adc" or "support".
• A match is a contest between two teams. Teams get a final result, "win" or "lose", for each match that they play in.
• Matches occur at a specified date and time. They also take place in uniquely named venues. In addition to names, venues have a location and a capacity. Only venues that are part of the OC need to be tracked.
• In a match, each player selects a single champion from a roster of pre-defined champions to play. Each champion may only be selected once in a match. A champion has a unique champion name, and also has a type (for example, "mage" or "support").
• Additional performance statistics need to be recorded for each champion being used by a particular player in a particular match. The three statistics to be recorded are a count of the number of each of the "kills", "deaths" and "assists" that they achieve (for example, in a particular match, a particular player playing a particular champion might achieve 4 kills, 2 deaths, and 3 assists).

According to the requirements above, give the ER diagram for the database using the UML class symbols (as used in the lecture notes and tutorials), making appropriate assumptions where necessary. You must represent entities, relationships and their attributes, and all applicable constraints in your diagram. Explain any constraints that cannot be expressed in the diagram.

Problem 5. ER to Relational Schema Mapping.

Consider the University database ER diagram below, shown using the UML class symbols. Some notes on the ER diagram:
• Classes within a course are identified by group numbers. Lecture classes always have groupNo zero ("0"), and tute classes are numbered as 1, 2, 3, etc.
• Classes are taught by staff members, of which some are tutors on contracts.

Map the diagram to a relational database schema. Indicate the primary key (underline) and any foreign keys (asterisk) for each relation.

Consider the University database ER diagram below, shown using the UML class symbols. Some notes on the ER diagram:
• Classes within a course are identified by group numbers. Lecture classes always have groupNo zero ("0"), and tute classes are numbered as 1, 2, 3, etc.
• Classes are taught by staff members, of which some are tutors on contracts.

Map the diagram to a relational database schema. Indicate the primary key (underline) and any foreign keys (asterisk) for each relation.

Figure 1 The ER model for a University database

Note: Need only Problem 2,3,4, and 5

Attachment:- Database Concepts.rar