Databases | SQL | Oracle | MS Access Answers

Bank Management System:

The main aim of the Bank

Management Mini DBMS project is

to keep record of customer

transactions in the bank. We aim to

demonstrate the use of create,

read, update and delete MySQL

operations through this project.

Firstly, employee registration is

done in the concerned bank

branch. Branch employee creates

a customer account in the bank,

then the customer can credit

amount, debit amount and check

balance. Customers can even use

different services like insurance,

loan, bill payments etc.

Consider the following scenario A student has a name, which consists of a given name and a family name, and a student IID. Each student is uniquely identified by his/her student ID. A course has a subject and a course ID. For each course, we want to record the number of students taking that course and the type of equipment being used for the course. A course is uniquely identified by its course ID. A student can be enrolled in an arbitrary number of courses, and an arbitrary number of students can be enrolled in a course. For each course in which they are enrolled students receive a lab mark and an exam mark. A course cannot exist if there is no student enrolled in it. A school is distinguished by the honour's degree that it awards. We also want to record to which faculty a school belongs. A student is registered with at most one school, while a school can have an arbitrary number of students.

a. Identify the entities, attributes and relationships.

b. Show the identifier/s for each entity

Consider the relations Authors(auID,name) and Authoring(articleID,authorID), containing information on names of authors, and who is authoring which papers, respectively. i) Write an SQL query that returns for each article, its ID, title and the number of authors. ii) Write an SQL query that returns the titles of articles authored by 'Robert Tarjan'. iii) Write an SQL query that returns the number of co-authors of 'Robert Tarjan'. (I.e., the number of authors who have written at least one article together with him.)

All are based on the following airline database schema:

Flights(flno, from, to, distance, departs)

Aircraft(aid. aname, range)

Certified(eid, aid)

Employees(eid, ename, salary)

By definition. pilots are those employees who are certified on at least onelireraft. An aireraft can be used for any fight provided t

has sufficient range. Pilots can pilot any flight provided they are certifiedn an aircraft with sufficient range.

Problems

1. Find eid's of pilots who are certified on some Boeing.

2. Find names of pilots who are certified on some Boeing.

3. Find aid's of aircraft that can fly non-stop from LA to NY. Assume jou don't already know the distance.

4. Find flno of flights that can be piloted by every pilot whose salary is (ver $100,000.

5. Solve problem 4 without using the division operator

Question 1 You have been tasked to design a database for use at a doctor’s private practice. There is only one doctor at the practice and many patients consult the doctor presenting various health issues. The patients pay the fees due in cash or may complete their medical aid forms. On consultation, the patient describes their symptoms and the doctor diagnoses a disease/ ailment that the patient may be suffering from. Some patients present symptoms as a sign to more than one disease. In cases where the doctor cannot assist the patient, they refer the patient to a specialist. a. List all the entities you can identify for the given requirements. b. Identify attributes for each of the identified entities, clearly marking the primary key/s. c. Which of the identified entities are related? Describe the cardinality of the relationships.

Create the ERD using UML notation to show the relationships between DENTIST, PATIENT and

SURGERY. Also show the multiplicity in the relationships and attributes.

• The patient must be examined by more than one dentist and a dentist can examine at least one patient;

• Dentist number, dentist name, office number, cellphone number and email address must b captured on the system;

• A dentist can examine more than the minimum of three patients per day;

• The following patient details need to be captured on the system: patient number, patient name, street address, email address;

• Each surgery is assigned an examination at the time as instructed by dental clinic policy.

Tshepo is a visionary leader with great ideas for his group of companies. But his background is in

electronic engineering, not software development. He has decided that he needs to understand

most of the jargon around databases and datamodels before he will feel comfortable with

participating in the process to design the carbon accounting database.

Answer his questions below, providing examples that are relevant to ACME Technologies

wherever possible.

Q.2.1 Tshepo wants to know what the difference is between one-to-many, many-to-many and

one-to-one relationships. For each of the below relationships, provide an explanation of

what it is and a relevant example.

Q.2.1.1 One-to-many relationship. (4)

Q.2.1.2 Many-to-many relationship. (4)

Q.2.1.3 One-to-one relationship.

1.1 The table shown below stores details of students and the overall grade each student

obtained in different modules. The Primary Key is (StudentID, ModuleID). (8)

Results

StudentID StudentName ModuleID ModuleName Grade

S001 Smith M01 Java A

S001 Smith M02 Databases B

S002 Ford M01 Java B

(i) Which Normal Form does the above table violate and why?

(ii) Give an example of an update anomaly and an example of a delete anomaly that may

occur if the table is left un-normalised. Explain the problems that are caused.

(iii) Show how you would normalise the table.

1.2 An important concept in the theory of relational databases is that of a functional (8)

dependency.

(i) Explain what is meant by a functional dependency and give an example.

(ii) Identify two functional dependencies in the following table (A, B and C are the

attributes):