Answered step by step
Verified Expert Solution
Question
1 Approved Answer
For each of the following situations, design a database to hold the specified information drawing an entity- relationship (ER) diagram and writing a database design
For each of the following situations, design a database to hold the specified information drawing an entity- relationship (ER) diagram and writing a database design outline for the database required to hold the information described in the problem. A database design outline is a description of a database using the following notation (shown here for an example we used in class). Primary key fields must be underlined and foreign keys annotated with "attributeforeign key to TABLE". CUSTOMER(CustomerID, FirstName, LastName, StreetAddress, City, State, Zip, Phone) LOAN(Loan D, Date, Loan Amount, Rate, Term, Type, CustID) CustID foreign key to CUSTOMER PAYMENT(LoanID, PaymentNumber, Date, Payment Amount) LoanID foreign key to LOAN In your answers, all tables must have a primary key; you may add synthetic-key "ID" fields to tables whenever necessary. An entity-relationship (ER) diagram means a graphical depiction of the database structure as given in class. For the example above, the diagram would be as follows: M CUSTOMER Takes out Takes out LOAN Made on PAYMENT In each question below, assume that any "address information" specified is in U.S. format, consisting of fields for street address, city, state code, and zip code. Assume that you do not have the master zip code table in your databases, so that city, state, and zip code may be treated as independent (this issue will be discussed in class on February 11). Note that any text in the diamonds (such as "takes out" and "made on" above) is essentially a comment relating the database structure to the business situation, so exactly what you write in the diamonds is not critical. When the problems specify that you should store a "date/time", assume that you will store dates and times together in MS-Access-style "date/time" fields. Question 4. (30 points) You are in charge of keeping track of Rutgers bus system. You operate a number of different kind of bus, for example, "LX or REXL. For each kind of bus, you want to keep track of the operating hours and days of operations. For example, A bus operates from 7:00 AM until 9:30 PM during the weekdays and H bus operates from 6:00 AM until 2:30 AM during the weekdays. For each bus, you want to store bus ID, date acquired, maximum mileage. Each kind of bus uses multiple buses, but, for simplicity, assume one bus can be used in exactly one kind of bus. If a bus 12255039 is LX bus, then the bus will be LX until it reaches the maximum mileage. The event of a bus leaving the first station is called a dispatch. Every time a bus dispatches, you want to record which bus was dispatched, who was the driver, the date/time the dispatch started, the date/time the dispatch ended, and up to 255 characters of text for driver comment (such as back door is not working). Each driver has a unique employee ID, along with a first name, last name, middle name, date of birth, cell phone number, and date of last safety training
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started