Question 1Figure 1 shows an ER schema for a database that may be used to keep track of part of aHospital Management System. Map this schema into a relational schema, and specify allprimary keys and foreign keys.NameDateTypeAddressDetailsSpecialization(1,3)DOCTORSTreatsPATIENTS|(0,N)(0,N)QualificationLogDiseaseTreatmen|(1,1)DateTESTSTypedescriptionFigure 1: An ER diagram for a part of a Hospital database.

Entity-set schema: DOCTORS(Name, Address, Specialization, Qualification) PATIENTS(Type, Details)…

Figure 1 shows an ER schema for a database that may be used to keep track of part of a Hospital Management System. Map this schema into a relational schema, and specify all primary keys and foreign keys. Name Address Specialization Date Type Details (1,3) DOCTORS Treats PATIENTS |(0,N) (0,N) Qualification Log Disease Treatmen |(1,1) Date TESTS Туре description

Figure 1 shows an ER schema for a database that may be used to keep track of part of a Hospital Management System. Map this schema into a relational schema, and specify all primary keys and foreign keys. Name Address Specialization Date Type Details (1,3) DOCTORS Treats PATIENTS |(0,N) (0,N) Qualification Log Disease Treatmen |(1,1) Date TESTS Туре description

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

Design a Relational Database by Mapping the following Entity-Relationship Diagram (ERD) into Relational Model. You must clearly determine the PK of each relation. Also, you should show the links between FKs and PKs in your answer. SName Owner SHIP MOVEMENT PName HISTORY HOME PORT SHIP PORT Latitude (0,") VISITS (0.") Timestamp Longitude N N N (1.1) IN TYPE ON 1 1 Date Time StartDate VISITS Type EndDate Tonnage SHIP TYPE Name STATE/COUNTRY Name SEA/OCEAN/LAKE Hull Continent
If you sell a house more than once, it is treated as different "property" for each sale. Draw an entity-relationship diagram and write a database design outline. Q2. Law Office: You are trying to automate the records for your uncle's law firm. Multiple attorneys work for the firm, and for each of them you want to store their first name, last name, date hired, hourly billing rate, and date promoted to partner (blank if the attorney is not a partner). Each attorney has multiple cases, which is identified by a unique "docket number". Each case has a name and description, is worked on by only one attorney, and involves a single client. For each client, you want to store a first name, last name, middle name, phone number, address, city, state, and zip code (assume you do not have a zip-code table). Finally, you want to keep track of the billable hours spent on each case. For a single case, this comprises information that looks like Hours Billed Date 2/24/2005 Description Prepare brief…
Relations describe the intersection between two axes, x and y. For example, a table (tuple) found in a database contains columns (attributes) and rows (records or observations). Database and Geospatial Mapping Application developers rely on tables and cartesian coordinate systems to adhere to the rules of relational database theory so that integrity in the location is preserved. Tables should only contain records that relate to the columns found in that table and the data domain determines what records should be stored in a given table. Database administrators can check for integrity in relationships within and between tables using structured query language (SQL). What other computer-science or IT jobs rely on relations?
Consider a PUBLICATION COMPANY database in which data requirements are summarized as follows: Every topic should contain name, code and research area. The topics can have several reports and the reports may contain several topics in them. The report should have a title. A department can publish more than one report. A report can be published only by a single department. The department must have its name and address. Reports can be written by many contractors and contractors can write many reports. Each contractor should have name and address. A publication can include several topics and a topic can be present in more than one publications. A publication must have its title and code. One department can have many publications and a publication can be made only by one department. Construct an E-R diagram (based on a Chen's model) to represent the above requirements. Make sure you include all appropriate entities, relationships, attributes, and cardinalities.
Your job as a database designer is to design a database with a schema that captures all the information that galleries need to maintain. Galleries keep information about artists, their names (which are unique), birthplaces, age, and style of art. For each piece of artwork, the artist, the year it was made, its unique title, its type of art (e.g., painting, photograph, sculpture), and its price must be stored. Pieces of artwork also classified into groups of various kinds, for example, portraits, still lives, works by Picasso, or works of the 19th century; a given piece may belong to more than one group. Each group is identified by a name (like those just given) that describes the group and property. Finally, galleries keep information about customers. For each customer, galleries keep that person’s unique ID, name, address, total amount of dollars spent in the gallery, and the artists and groups of art that the customer tends to like. Draw the ER diagram for the database.
Problem: design a er diagram for a database to keep track of building inspections required by a department of Damaged Buildings (DB). DB building inspections are requested by builders every month. Each building inspection is either passed or not. Inspections have a type code (3 characters, e.g. PLU, FRM, ELE, etc.) and possibly sequencing requirements. Some inspections cannot be performed before other inspections, e.g. final plumbing inspection cannot be performed until the framing inspection is passed. Each inspection has a numeric score, with 75 or more out of 100 being sufficient for a pass status. Each inspection data contains the date of inspection, inspector identification, inspection score, and textual information about the inspection. The textual information can be updated later, but the score can never be changed. FODB maintains a pool of inspectors. Each inspector has a unique 5 digit employee ID, name, and date they were hired. They can conduct any type of inspection but…
Suppose that the initial conceptual design of the Car Rental database consisted of just a single relation as follows:Rental(VehicleID, CustomerID, DateOut(PK), DateReturned, VehicleClass, RentalRate, CustomerFName, CustomerLName, Balance) Normalize the Rental relation to 2NF and 3NF
Question A city college would like to maintain their academic information in a MySql database system. The following are the interested information: Courses: Catalog # (M170 for instance) Credit Hours; Name of the Course (Database Design Fundamentals for instance) Course Descriptions Prerequisite Components (Lecture, or Laboratory for instance) Classes: Class # Class Name (usually the course name, M 170 for instance) Start Date End Date Meeting time (9:30 am ~ 12:15 pm, for instance) Days in a week (Monday) Term (Spring 2018, for instance) Location (TIE Building for instance) Room (UB 301, for instance) Instructor Online status (online, or in-person) Faculty: Employee Id (001880301, for instance) First Name Last Name Title (professor, for instance) Email Phone Office Location (TIE building, for instance) Room (UB303, for instance) Student: Student ID First Name Last Name Plan Sub Plan Advisor Current GPA Grade of each course The Term you got the grade for a course. The class…
Consider a database with the following three tables: RACES[RaceName, Year, Organizer, Country, Federation, Type, Distance] RaceName and Year form the primary key. Type is from { Run, Triathlon, Bike, MountainBike }. Distance is given in meters. RESULTS[RaceName, Year, Place, AthleteName] (RaceName, Year, Place) form the primary key. (RaceName, Year) form a foreign key referencing RACES. RECORDS[RaceName, Year, Record, Time] (RaceName, Year, Record) form the primary key. The foreign key is the same as for RESULTS. The values of Record could be, e.g., World Record, National Record, Fastest Time. Time is in seconds. Write queries in SQL that produce the answers to the following questions: (a) Find the names of all athletes who exclusively participate in runs that are longer than 40 kilometers. (b) Find organizers who organized a mountain-bike and a bike race, where the mountain-bike race was longer than the bike race. (c) Find the names of all athletes who achieved a world record in…
A database at a college must support the following requirements: For a department, store its number and name. For an advisor, store his or her number, last name, first name, and department number to which the advisor is assigned. For a course, store its code and description (for example, MTH110, Algebra). For a student, store his or her number, first name, and last name. For each course, the student takes, store the course code, course description, and grade earned. Also, store the number and name of the student’s advisor. Assume that an advisor might advise any number of students but that each of the students has just one advisor. Design the databases for the preceding set of requirements. Use your own experience as a student to determine any functional dependencies. List the tables, columns, and relationships. Also, represent your design with an E-R diagram.
QUESTION 1 15 points We have been asked to develop a database system for a tourism office. The system needs to keep information about customers, cities around the world and hotels in these cities. Each city has name, country and weather that is made up of average temperature in summer and average temperature in winter. Different cities can have the same name in different countries, but for one country the city names are unique. For each city, the system needs to keep information about the number of hotels in the city. For each hotel, the system must store the hotel name (which is unique for a given city), rating, contact (Email, Telephone, fax) and hotel address. The hotel rating is an integer number between 1 to 5, where 1 indicates a very low rating and 5 indicates a very high rating. For each hotel we need to store the number of rooms for each type, for example: a hotel may have 20 rooms of type single and 15 rooms of type double. The system also store the reservations made by the…