Transcribed TextTranscribed Text

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 time. (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 relational database. 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 cars. (viii) Create a view containing all the details in the car table and the gross profit. PERSON *personid persfname perslname CAR *carid carcost carsell cartype 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.

Solution PreviewSolution Preview

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.

    By purchasing this solution you'll be able to access the following files:

    for this solution

    PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

    Find A Tutor

    View available Database Development Tutors

    Get College Homework Help.

    Are you sure you don't want to upload any files?

    Fast tutor response requires as much info as possible.

    Upload a file
    Continue without uploading

    We couldn't find that subject.
    Please select the best match from the list below.

    We'll send you an email right away. If it's not in your inbox, check your spam folder.

    • 1
    • 2
    • 3
    Live Chats