DBMS(5sem Comp), MIS ILOC, OOPM: November 2019

Prepared By Prof Saiqa Khan

M H SABOO SIDDIK COE

Q.1 Consider the university database that keeps track of student and their mayor’s, transcripts, registration and the university course. Several sections of each course are offered and each section is related to the instructor who is teaching. IT also keeps track of the sponsored research projects of faculty and graduate students of the academic departments of the particular college. The database also keeps track of research grants and constracts awarded to the university. A grant is related to one principle investigator and to all researchers it support.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert 3 tuples in them.

(d)Execute SQL queries using following commands:

1. Like 2.union

Q.2 ABC Engg. College is graded A college. It is five departments. The departments are headed by senior most & qualified faculty. The placement of final year students from all branches is managed by placement centre. Placement centre is managed by one of the faculty from any department. The teaching load of that faculty is zero. To assist placement centre head there are placement secretaries (whose teaching load is 13) from each department along with placement assistance from students (selected by placement center) of all five departments. Placement centre is responsible for on −campus & off campus recruitment of students. The placement process requires students resume & relevant documents along with approval from placement centre.

Companies invited on campus conduct test followed by interviews. The criteria of selection depend on academic performance & interview. For off−campus placements placement centre head must accompany students to the venue.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them.

(d)Execute SQL queries using following commands:

1. intersection 2.Minus

Q.3 Consider the following relations :

BOOKS (Book#, Primary_autor, Topic, Total_stock, $price)

BOOKSTORE (Store#, City, State, Zip, Inventory_value)

STOCK (Store#, Book#, Qty)

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them.

(d)Execute SQL queries using following commands:

1. Like 2.INTERSECTION

Q.4 Consider a banking system where each bank has multiple branches and each branch can have multiple

accounts and loans. Account and loan can be of different types.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. left outer join 2.intersection

Q.5 Blood bank is a critical' entity in providing required type of blood to the patients at critical time. Their database

keeps track of the inventory of the blood, together with relevant information like blood group, date received,

location, date of expiry, donor, etc.

The database keeps information such as name, address~ and telephone' number of other blood banks in the area.

The reason for doing so is to get blood of a particular from other bank in case of emergency.

Information about donors is recorded as well. Donors are classified into occasional and regular donors. For the

regular donors, the database keep information such as identification number, blood type and a history of their

donations.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. sum aggregate function 2.like

Q.6A list of health care providers in the area along with information such as address, telephone number, etc. is kept.

The healthcare providers are the customers of the blood bank. They keep track of the blood transactions

performed. These transactions are classified into : normal transactions and unexpected transactions (for

example, the motor accidents during the holiday season). The reason for keeping track of the unexpected

transactions is to use this information in estimating the extra amount of blood to keep in the inventory for each

age group during the coming holiday season.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. count aggregate function 2.right outer join

Q.7 Consider the global schema :

PATIENT (number, name, ssn, amount_due, dept, doctor, med_treatment)

DEPARTMENT (dept, location, director)

STAFF (staffnum, director, task)

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. max aggregate function 2.not exists

Q.8 A financial institution offers different investments schemes to its customers such as shares and debentures.

The company also accept fixed deposits from the general public, institutions and its employees. The

company keeps a database with valuable information about its customers (such as id, name and address)

and financial instruments.

Fixed deposit have varying terms of 1, 2 or 3 years.

The company also raises debentures periodically. The debentures may be either convertible or non

convertible.

Company also calls shares periodically. The company issues two kind of shares : equity shares and

preferred shares.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. min aggregate function 2.full outer join

Q.9 Consider the following description for sales order documents.

“An order document is comprised of several sales orders. Each individual order has a number and it

contains the customer information, the date when the order was received, and the items ordered. Each

customer has number, name, street, city and zip code. Each item has an item order, parts information and

quantity. The parts information contains a number, a description of the product and its unit price. The

number should treated as attribute when considering XML representation of the document.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. avg aggregate function 2.null value

Q.10

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. nested query using in 2.like

Q.11

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. nested query using ANY 2.union

Q.12 A university has many academic unit named schools. Each school is administered by a Head of School. The school has administrative and teaching staff. A school offers many courses. A course consists of many subjects A subject is taught to students who have registered for a subject in a class room by a teacher.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. any nested query 2.intersection

Q.13 The university has examination centers and student request/register for examination. The students are issued hall tickets. An examination center has many classrooms where examinations are conducted as per a defined schedule.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. nested query using in 2.like

Q.14 department store consists of many item sections. A section is in charge. The store has three kinds of employees: accounts and billing; administrators; section maintainers. A request for purchase of items for the store is initiated by accounts and billing department which has the inventory details. Purchases are made by administration with proper quality checks from specified vendors. The section maintainers update the inventory placed at shelf’s.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. max aggregate function 2.sum aggregate function

Q.15

SUPPLIERS (S#, SNAME, STATUS, CITY)
PARTS (P#, PNAME, COLOUR, WEIGHT, CITY)
PROJECTS (J#, JNAME, CITY) SPJ (S#, P#, J#, QUANTITY)

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. exists clause 2.null value

Q.16

Emp(EmpNo, Ename, DNo, Sal)

Dept(DeptNo, Dnarne, DMgrNo)

Proj(ProjNo, ProjName, DNum)

Works-on(EmpNo, PNo, Hrs)

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. with clause 2.like

Q.17

Consider a University like IITs having different Engineering departments. The University offers PhD, Master of Technology and Bachelor of Technology programmes in various Engineering disciplines. Each department has a number of full time faculty members - Professors, Associate Professors and Assistant Professors. Several Guest faculties can also be visiting the University. A student may take up only one programme at a time from the University.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. with clause 2.sum aggregate function

Q.18

Consider the following relations:

Faculty (fa_id, fa-name, f-designation)

Student(st_ id, st_name, st_address, st_programme, st_programmeRegistrationdate)

Project_Consultation (fa_id, pa_id, pc_consultationDate, pc_hours)

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. nested query using in 2.count aggregate function

Q.19

A University like IGNOU maintain a list of published blocks and its sales in its store management system. A block is a part of a course. All these publications have an ISBN number, block title, price and the course code this block belongs to. A student can purchase one or more blocks from the store.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. nested query using in 2.like

Q.20

A database Management system is to be created for a Study Centre to keep track of the students and the assignment records of the students. The database records the date of submission of assignment, the date of evaluation, the date of viva, the date of declaration of results, who evaluated the assignment, and the mark list description in which the assignment marks were sent to Regional Office. The database also maintains the details of the evaluators.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. nested query using in 2.left outer join

Q.21

Consider the following relations of a business organization:

Consumers (C_code, C_name, C_phone)

Items (I_code, I_desc, I_price)

Bill_consumer (Bill_id, C_code, Bill_date)

Bill_items (Bill_id, I_code, Quantity)

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. right outer join 2.like

Q.22

A Library maintains the database of its members and book loans using a database system having the following relations:

LIBRAY_ITEM (item_id, item_title, authornames)

MEMBER (m_id, m_name, m_contactphone, m_dob)

LOAN (m_id, item_id, dateofgivingloan)

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. nested query using in 2.count

Q.23 A University offers different types of courses – Post Graduate Degrees, Under Graduate Degrees, and Certificates. All these degrees can be obtained either by full time students or part time students. To award a degree a student must successfully complete courses worth a minimum of 72 credits for the Post Graduate Degree, 96 credits for the Graduate Degree and 16 credits for the Certificate programmes. The University offers a number of subjects to its students, which may worth 2 credit points, 3 credit points and 4 credit points. To successfully complete a course a student must get a grade of ‘C’ in that subject.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. intersection 2.max function

Q.24

Database for a car-insurance company whose customers own one or more cars each. Each car has associated with it zero to any number of recorded accidents.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. min function 2.avg function

Q.25

Database for a hospital with a set of patients and a set of medical doctors. Associate with each patient a log of the various tests and examinations conducted.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. with clause 2.max function

Q.26

A university registrar’s office maintains data about the following entities: (a) courses, including number, title, credits, syllabus, and prerequisites; (b) course offerings, including course number, year, semester, section number, instructor(s), timings, and classroom; (c) students, including student-id, name, and program; and (d) instructors, including identification number, name, department, and title.

Further, the enrollment of students in courses and grades awarded to students in each course they are enrolled for must be appropriately modeled.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. like function 2.avg function

Q.27

Consider a database used to record the marks that students get in different exams of different course offerings.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. full outer join 2.like function

Q.28

Database for keeping track of the exploits of your favourite sports team. You should store the matches played, the scores in each match, the players in each match and individual player statistics for each match. Summary statistics should be modeled as derived attributes

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. min function 2.null value

Q.29

Consider the database which models an online bookstore.

Suppose the bookstore adds music cassettes and compact disks to its collection. The same music item may be present in cassette or compact disk format, with differing prices. model the case where

Model a case where shopping basket may contain any combination of books, music cassettes, or compact disks.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. max function 2.count function

Q.30

Consider a university database for the scheduling of classrooms for final exams.

This database could be modeled as the single entity set exam, with attributes

course-name, section-number, room-number, and time. Alternatively, one or more

additional entity sets could be defined, along with relationship sets to replace

some of the attributes of the exam entity set, as

• course with attributes name, department, and c-number

• section with attributes s-number and enrollment, and dependent as a weak

entity set on course

• room with attributes r-number, capacity, and building

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. min function 2.avg function

Q.31

Database for a university registrar’s office. The office maintains data about each class, including the instructor, the number of students enrolled, and the time and place of the class meetings. For each student–class pair, a grade is recorded.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. max function 2.sum function

Q.32 Consider the following set of requirements for a university database that is used to keep track of students’ transcripts.

a. The university keeps track of each student’s name, student number, social security number, current address and phone, permanent address and phone, birthdate, sex, class (freshman, sophomore, . . ., graduate), major department, minor department (if any), and degree program (B.A., B.S., . . ., Ph.D.). Some user applications need to refer to the city, state, and zip code of the student’s permanent address and to the student’s last name. Both social security number and student number have unique values for each student.

b. Each department is described by a name, department code, office number, office phone, and college. Both name and code have unique values for each department.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. exists function 2.natural join

Q.33 Consider the following set of requirements for a university database that is used to keep track of students’ transcripts.

a. Each course has a course name, description, course number, number of semester hours, level, and offering department. The value of course number is unique for each course.

b. Each section has an instructor, semester, year, course, and section number. The section number distinguishes sections of the same course that are taught during the same semester/year; its values are 1, 2, 3, . . ., up to the number of sections taught during each semester.

c. A grade report has a student, section, letter grade, and numeric grade (0, 1, 2, 3, or 4).

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. null function 2.avg function

Q.34

Design database for keeping track of information about votes taken in the U.S. House of Representatives during the current two-year congressional session. The database needs to keep track of each U.S. STATE’s Name (e.g., Texas, New York, California) and includes the Region

of the state (whose domain is {Northeast, Midwest, Southeast, Southwest, West}). Each CONGRESSPERSON in the House of Representatives is described by their Name, and includes the District represented, the StartDate when they were first elected, and the political Party they belong to (whose domain is {Republican, Democrat, Independent, Other}). The database keeps track of each BILL (i.e., proposed law), and includes the BillName, the DateOfVote on the bill, whether the bill PassedOrFailed (whose domain is {YES, NO}), and the Sponsor (the congressperson(s) who sponsored—i.e., proposed—the bill). The database keeps track of how each congressperson voted on each bill (domain of vote attribute is {Yes, No, Abstain, Absent}).

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. like function 2.sum function

Q.35

A database is being constructed to keep track of the teams and games of a sports league. A team has a number of players, not all of whom participate in each game. It is desired to keep track of the players participating in each game for each team, the positions they played in that game, and the result of the game. Choose your favorite sport (soccer, baseball, football, . . .)

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. min function 2.avg function

Q.36

Consider an entity type SECTION in a UNIVERSITY database, which describes the section offerings of courses. The attributes of SECTION are: SectionNumber, Semester, Year, CourseNumber, Instructor, RoomNo (where section is taught), Building (where section is taught), Weekdays (domain is the possible combinations of weekdays in which a section can be offered {MWF, MW, TT, etc.}), and Hours (domain is all possible time periods during which sections are offered {9–9.50 A.M., 10–10.50 A.M., . . ., 3.30–4.50 P.M., 5.30–6.20 P.M.,

Assume that SectionNumber is unique for each course within a particular semester/year combination (that is, if a course is offered multiple times during a particular semester, its section offerings are numbered 1, 2, 3, etc.).

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. min function 2.exists function

Q.37

Consider the BANK ER schema of Figure 03.17, and suppose that it is necessary to keep track of different types of ACCOUNTS (SAVINGS_ACCTS, CHECKING_ACCTS, . . .) and LOANS (CAR_LOANS, HOME_LOANS, . . .). Suppose that it is also desirable to keep track of each account’s TRANSACTIONs (deposits, withdrawals, checks, . . .) and each loan’s PAYMENTs; both of these include the amount, date, and time.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. min function 2.avg function

Q.38

The following narrative describes a simplified version of the organization of Olympic facilities planned for the 1996 Olympics in Atlanta.

The Olympic facilities are divided into sports complexes. Sports complexes are divided into one-sport and multisport types. Multisport complexes have areas of the complex designated to each sport with a location indicator (e.g., center, NE-corner, etc.). A complex has a location, chief organizing individual, total occupied area, and so on. Each complex holds a series of events (e.g., the track stadium may hold many different races). For each event there is a planned date, duration, number of participants, number of officials, and so on. A roster of all officials will be maintained together with the list of events each official will be involved in. Different equipment is needed for the events (e.g., goal posts, poles, parallel bars) as well as for maintenance. The two types of facilities (one-sport and multisport) will have different types of information.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. sum function 2.like function

Q.39

Design a database to keep track of information for an art museum. Assume that the following requirements were collected:

• The museum has a collection of ART_OBJECTs. Each ART_OBJECT has a unique IdNo, an Artist (if known), a Year (when it was created, if known), a Title, and a Description. The art objects are categorized in several ways as discussed below.

• ART_OBJECTs are categorized based on their type. There are three main types: PAINTING, SCULPTURE, and STATUE, plus another type called OTHER to accommodate objects that do not fall into one of the three main types.

• A PAINTING has a PaintType (oil, watercolor, etc.), material on which it is DrawnOn (paper, canvas, wood, etc.), and Style (modern, abstract, etc.).

• A SCULPTURE has a Material from which it was created (wood, stone, etc.), Height, Weight, and Style.

• An art object in the OTHER category has a Type (print, photo, etc.) and Style.

• ART_OBJECTs are also categorized as PERMANENT_COLLECTION that are owned by the museum (which has information on the DateAcquired, whether it is OnDisplay or stored, and Cost) or BORROWED, which has information on the Collection.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. sum function 2.exits function

Q.40

Database for Hospital management system:

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. ANY JOIN 2.with clause

Q.40

Database for Airlines Reservation:

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. count function 2.null value function

Q.42

Database for Shopping Mall:

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. sum function 2.null value function

Q.43

Database for Art Gallery:

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. not exists function 2.like function

Q.44

Database for Criminal department:

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. sum function 2.exists function

Q.45

Database for General store management:

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. min function 2.intersection

Q.46

Database for Shopping Mall:

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. with clause 2.union

Q.47

Database for Hotel management:

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. minus function 2.like function

Q.48

Database for Car database management:

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. avg function 2.like function

Q.49

You need to design a database for an Art Gallery.The database schema must keep

information about artists, their names (which are unique), birthplaces,age, and

style of art and a photograph. For each piece of artwork, the artist, the year it

was made, its unique title, its type of art (e.g., painting, sculpture, photograph),

and its price, along with the picture (Thumbnail) must be stored. The database

also stores information about Customers.

For each customer, the database stores that person's unique name, address, and

total amount of money spent in the gallery and the artists and type of art that

the customer tends to like (can be a text).

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. avg function 2.like function

Q.50

A Database is to be designed for a medium sized company dealing with industrial application of computers. The company delivers various products to its customers ranging from a

single application program through to complete installation of hardware with customized

software. The company employs various expert, consultants and supporting staff. All

personal are employed on long term basic Let there are no short-term or temporary staffs.

Although the company is somehow structured for administrative purposes (that is, it is

divided into department headed by Department managers) all projects are carried out

in an inter-disciplinaryway. For each project a project team is selected, grouping employees

from different department, and a Project Manager (also an employee of the Company)

is appointed who is entirely and exclusively responsible for the control of the project,

quite independently of the Company hierarchy.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. SUM function 2.like function

Q.51

In a company database, we need to store information about employees, departments, and children

of employees. For each employee, identified by an emp.no ; we must record the number of years

worked, phone number and a photograph for identification. There are two classes of employees,

regular and contract. The salaries for both are calculated differently. For the regular employees, we

must record the name of the children and their ages. For each photo depending on its type, a display

Method is defined. For each department we must record dept.no., deptname, budget, and employees

who work in that department.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. avg function 2.EXISTS function

Q.52

A General Hospital consists of a number of specialized wards (such as Maternity,

Paediatry, Oncology, etc).Each ward hosts a number of patients, who were admitted on

the recoJ,1lmendatioriof their own GP and confirmed by a consultant employed by

Hospital. On admission, the personal details of every patient are recorded. A separate

, prescribed treatment. A number of tests may be conducted for each patient. Each patient

. is assigned to one leading consultant but may be examined by another doctor, if required

.Doctors are specialists in some branch of medicine and may be leading consultant for a

number of patients, not necessarily from the same ward.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. avg function 2.NOT EXISTS function

Q.53

Consider the following database for an Insurance company.

Customer (CustID, Custname, Custaddr, Custage)

PolicyType (PolicyID, Pname, Premiumamt, Type, NoofYears)

Agent (AgentId, Agentname, Agentzone)

Policy(CustID, PolicyID, AgentID, DependentName)

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. COUNT function 2.like function

Q.54

Design and implement a database that manages information about Hospital. Some information includes:

(i) Permanent doctors get fixed salary. Personal information like name, address, date of birth,

etc. required. Consulting doctors visits at fixed time everyday. Information like name contact

number, specialization, charges etc are required.

(ii) Patients are admitted to the hospital. Personal information like name, address, relative's name

and address patient's blood group, reason of admission, etc are required.

(iii) Patient are admitted to room of different types, per day charges depend on room type.

(iv) Various labs in hospital, where several tests conducted on patient each test has fixed charges.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. SUM function 2.like function

Q.55

Database is to be designed for a college to monitor students progress throughout their course of study. 20

The student are reading for a degree (such as B.A., B.Com., M.Sc. etc) within the Framework of the

modular system. The college provides a number of Modules, each being characterized by its code,

title, credit value, module leader who shares teaching duties with one or more lecturers. A lecturer

may teach (and be a module leader for) more than one module. Students are free to choose any module

they wish but the following rules must be observed: Some modules require prerequisites module and

some degree programs have compulsory modules. The database also contains some information about

students including their number, names address Degrees they read for and their past performance

(i.e. module taken and examination results)

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. avg function 2.like function

Q.56

The ABC. Bank offers five types of Accounts : loan, checking, savings, daily interest saving and money market. It operates a number of branches within the country. A client of the bank can have any number of accounts. Accounts can be self or a joint account.

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. avg function 2.null value function

Q.57

Database for Tours and travels:

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. LIKE function 2.union

Q.58

Database for Blood bANK:

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. MAX function 2.intersection

Q.59

Database for ONLINE BANK:

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. max function 2.MINUS

Q.60

Database for Placement Company:

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. min function 2.union

Q.61

Database for Manufacturing Company:

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. count function 2.intersection

Q.62

Database for Art Gallery:

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. not exists function 2.like function

Q.63

Database for Criminal department:

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. sum function 2.exists function

Q.64

Database for Library management:

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. sum function 2.like function

Q.65

Database for General store management:

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. min function 2.intersection

Q.66

Database for Shopping Mall:

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. with clause 2.union

Q.67

Database for Hotel management:

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. minus function 2.like function

Q.68

Database for Car database management:

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. avg function 2.like function

Q.69

You need to design a database for an Art Gallery.The database schema must keep

information about artists, their names (which are unique), birthplaces,age, and

style of art and a photograph. For each piece of artwork, the artist, the year it

was made, its unique title, its type of art (e.g., painting, sculpture, photograph),

and its price, along with the picture (Thumbnail) must be stored. The database

also stores information about Customers.

For each customer, the database stores that person's unique name, address, and

total amount of money spent in the gallery and the artists and type of art that

the customer tends to like (can be a text).

(a)Draw EER diagram.

(b)Convert EER model into Relational model.

(c)Create any four tables showing primary key, foreign key in Oracle and insert three tuples in them .

(d)Execute SQL queries using following commands:

1. avg function 2.like function

Q.70