Q1: Our Friend Los Gatos realizing that his wallcovering business had a few wrinkles in it, decide to pursue a law degree at night. After graduating, he has teamed up with Lyla El Pajaro to form Peck and Paw, Attorneys at Law. Los and Lyla have hired you to design a database system based upon the following set of business rules. It is in your best interest to perform a though analysis, to avoid needless litigation. Please create an ERD based upon the following set of rules:
An ATTORNEY is retained by one or more CLIENTS for each CASE. Attributes for ATTORNEY are Attorney ID, Name Address, City, State, Zip Code, Specialty (may be more than one), and Bar (may be more than one). A CLIENT may have more than one ATTORNEY for each CASE.
Attributes for CLIENT are Client ID, Name, Address, State, Zip Code, Telephone, and Date of Birth. A CLIENT may have more than one CASE.
Attributes for CASE are Case ID, Case Description, and Case Type. An ATTORNEY may have more than one CASE.
Each CASE is assigned to one and only one COURT.
Attributes of COURT are Court ID, Court Name, City, State, and Zip Code. Each COURT has one or more JUDGES assigned to it.
Attributes for JUDGE are Judge ID, Name, and Year In Practice. Please state any assumptions that you have made. Also, draw a data model for this situation.
Q2: develop an EER model for the following situation.
An international school of technology has hired you to create a database management system to assist in scheduling classes. After several interviews with the president, you have come up with the following list of entities, attributes, and initial business rules: Room is identified by Building ID and Room No and also has a Capacity. A room can be either a Lab or a Classroom. If it is a classroom, it has an additional attribute called Board Type. Media is identified by MType ID and has attributes of Media Type and Type Description. Note: her we are taking type of media (such as a VCR, projector, etc.), not the individual piece of equipment. Tracking of equipment is outside of the scope of this project. Computer is identified by CType ID and has attributes Computer Type, Type Description, Disk Capacity, and Processor Speed. Please note: As with Media Type, we are tracking only the type of computer, not an individual computer. You can think of this as a class of computers (e.g., PIII 900MHZ). Instructor has identifier Emp ID and has attributes Name, Rank, and Office Phone. Timeslot has identifier TSIS and has attributes Day of Week, Start Time, and End Time. Course has identifier Course ID and has attributes Course Description and Credits. Course can have one, none, or many prerequisites. Courses also have one or more sections. Section has identifier Section ID and attributes Enrollment Limit. After some further discussion, you have come up with some additional business rules to help you create the initial design: An instructor teaches one, none, ore many sections of a course in a given semester. An instructor specifies preferred time slots.
Scheduling data are kept for each semester, uniquely identified by semester and year. A room can be scheduled for one section or no section during one time slot in a given semester of a given year. However, one room can participate in many schedules, one schedule, or no schedules, or no schedules; one section can participate in many schedules, one schedule, or no schedules. A room can have one type of media, several types, or no media. Instructors are trained to use one, none, or many types of media. A lab has one or more computer types. However a classroom doesn’t have any computers. A room can’t be booth a classroom and a lab. There are also no other room types to be incorporated into the system.
============================================================================= Q5. Write SQL commands for the following:
a) Create two different forms of the INSERT command to add a student with a...
Please join StudyMode to read the full document