Database Management Systems
1. Draw data models for the following situation. In each case, make certain that you show
at least three attributes and key for each entity:
(a) A famer can have many cows, but a cow belongs to only one farmer.
(b) Farmers can own cows or share cows with other famers.
(c) An aircraft can have many passengers, but a passenger can be on one flight at a
(d) A patient can have many physicians, and a physician can have many patients.
(e) An art researcher has asked you to design a database to record details of artists and
the museums in which their paintings are displayed. For each painting, the
researcher wants to know the size of the canvas, year painted, title, and style. The
nationality, date of birth, and death of each artist must be recorded. For each
museum, record details of its location and specialty, if it has one.
2. For each of the data models of question 1, give the SQL statements to create tables for
3. Following is a data model for a car dealer:
(a) Create tables for this model.
(b) Insert 10 records to the tables you created (make up some values for each table).
(c) Answer the following queries and show the output of your queries:
(i) What is the personid of a particular name?
(ii) List sales personnel sorted by last name and within last name, first name.
(iii) List details of the sales made by one person (given the name of the person).
(iv) Report the number of cars sold of each type.
(v) What is the average selling price of cars sold by a particular person?
(vi) What was the maximum selling price of any car?
(vii) Report the gross profit made by each salesperson who sold at least three
(viii) Create a view containing all the details in the car table and the gross profit.
4. Create the following tables
Student (snum: integer , sname: string , major: string , level: string , age: integer )
Class (name: string , meets at: string , room: string , fid: integer )
Enrolled (snum: integer , cname: string )
Faculty (fid : integer , fname: string , deptid: integer )
The underline indicates the primary key of the table. The meaning of these relations is
straightforward; for example, Enrolled has one record per student-class pair such that the
student is enrolled in the class.
Upload the data files posted on blackboard to these tables. Write and run the following
queries in SQL. No duplicates should be printed in any of the answers. Show your query
and the output of your query.
(a) For each level, print the level and the average age of students for that level.
(b) For all levels except JR, print the level and the average age of students for that level.
(c) Find the names of students who enrolled in the class “Data Structures”.
(d) Find the names of students who have enrolled in at least one class.
(e) Find all classes that student “Maria White” enrolled.
(f) Find the classes that are taught by the faculty member “Linda Davis”.
(g) Find the faculty name and student numbers for the class “Social Cognition”.
5. Develop an UML class diagram for an experimental forestry system as described below.
Identify weak entity class (and its key), if there is any.
Each tree is represented. The information about a tree is its tree number, date planted, tree
location, measurements made on a tree, tree propagation of trees (parent tree), tree species,
and the forest in which it is located.
Each species is represented. The information about a tree species is its name, type of wood
yielded (hard, soft), and maximum height of the species.
Each measurement is represented. The information about a measurement is its number,
result of measurement, type of measurement, and date of measurement.
Each forest is represented. The information about a forest is its name, size, the company
that owns the forest, the state in which it is located, and the foresters that manage the forest.
Each forester is represented. The information about a forester is their name, their social
security number, their age, and their address. Each forester manages exactly one forest.
These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction
of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice.
Unethical use is strictly forbidden.