You have just been hired as a database administrator and have been assigned the task of implementing a system for consumer loans. The bank requires complete information about every loan (the amount, interest rate, term, and so on). It also needs the data about the customers holding those loans (name, address, telephone, etc.). Your job is to design a database that will satisfy the requirements of the bank. The database is straightforward and will contain a customers table and a loans table.
The bank would also like the database to track the payments for each
loan. One loan will receive many payments, but a given payment is
associated with only one loan. A final challenge in this initial
case is to introduce a table for the loan officers into the database.
The loan officer is an employee of the bank who approves (disapproves)
a customer's request for a loan. One loan officer is associated with
many loans, but a specific loan is associated with only one officer.
Design a database that will include the necessary data to provide all
of the information you need. Show the required tables in the database,
being sure to indicate the primary key and foreign keys in each table.
Indicate one or two other fields in each table (you need not list them
all).
Design a database for a recreational sports league that will monitor
players, coaches, and sponsors. There may be any number of teams
in the league, with each team having any number of players. A player
is associated with only one team.
Each team has one coach. Only one coach is allowed per team.
The league also imposes the rule that a person may not coach more than
one team. Each team has a sponsor, such as a local business.
One sponsor can be associated with multiple teams.
Your solution should make the system as realistic as possible.
The player table, for example, requires not only the identifying information
for each player (name, address, phone, and so on) but additional fields
such as birth date (to implement age limits on various teams), ability
ratings, and so on. Your system should be capable of producing reports
that will display all information about a specific team, such as its players,
coach, and sponsor. The league administrators would also like master
lists of all teams, players, coaches, and sponsors.
Show the required tables in the database, being sure to indicate the
primary key and foreign keys in each table. Indicate one or two other
fields in each table (you need not list them all).
The Personnel Director
You have been hired as a personnel director for a medium-sized company
with offices in several cities. You require the usual personal data
for each employee (birth date, hire date, home address, and so on).
You also need to reach an employee at work, and must be able to retrieve
the office address, office phone number, and office fax number for each
employee. Each employee is assigned to only one branch office.
Your duties also include the administration of various health plans
offered by the company. Each employee is given his or her choice
of several health plans.
Each plan has a monthly premium and deductible amount. Once the
deductible is reached, each plan pays a designated percentage of all subsequent
expenses.
Design a database that will include the necessary data to provide all
of the information you need. Show the required tables in the database,
being sure to indicate the primary key and foreign keys in each table.
Indicate one or two other fields in each table (you need not list them
all).
The management of a national restaurant chain is automating its procedure
for monitoring its restaurants, restaurant owners (franchisees), and the
contracts that govern the two. Each restaurant has one owner (franchisee).
There is no limit on the number of restaurants an individual may own, and
franchisees are encouraged to apply for multiple restaurants.
The payment from the franchisee to the company varies according to
the contract in effect for the particular restaurant. The company
offers a choice of contracts, which vary according to the length of the
contract, the franchise fee, and the percentage of the restaurant's sales
paid to the company for marketing and royalty fees. Each restaurant
has one contract, but a given contract may pertain to many restaurants.
The company needs a database capable of retrieving all data for a given
restaurant, such as its annual sales, location, phone number, owner, and
type of contract in effect. It would also like to know all restaurants
owned by one person as well as all restaurants governed by a specific contract
type.
The Widgets of America Corporation gives its sales staff exclusive rights
to specific customers. Each salesperson has many customers, but a
specific customer always deals with the same sales representative.
The company needs to know all of the orders placed by a specific customer
as well as the total business generated by each sales representative.
The data for each order includes the date the order was placed and the
amount of the order. Design a database capable of producing the information
required by the company.
Show the required tables in the database, being sure to indicate the
primary key and foreign keys in each table. Indicate one or two other
fields in each table (you need not list them all).
Design a database for a medical research project that will track specific
volunteers and/or specific studies. A study will require several
subjects, but a specific person may participate in only one study.
The system should also be able to track physicians. Many physicians
can work on the same study. A given physician may also work on multiple
studies.
The system should be able to display all facts about a particular volunteer
(subject) such as name, birth date, sex, height, weight, blood pressure,
cholesterol level, and so on. It should be able to display all characteristics
associated with a particular study; for example, the title, beginning date,
ending date, as well as the names of all physicians who work on that study.
It should also show whether the physician is a primary or secondary investigator
in each study.
Show the required tables in the database, being sure to indicate the
primary key and foreign keys in each table. Indicate one or two other
fields in each table as appropriate. (You need not list them all.)
You have been hired as a consultant to a securities firm that wants
to track its clients and the stocks they own. The firm prides itself
on its research and maintains a detailed file for the stocks it follows.
Among the data for each stock are its symbol (ideal for the primary key),
the industry it is in, its earnings, dividends, etc.
The firm requires the usual client data (name, address, phone number,
social security number, etc.). One client can hold many stocks, and many
clients can hold one stock. The firm needs to know the date the client
purchased the stock, the number of shares that were purchased, and the
purchase price.
Show the required tables in the database, being sure to indicate the
primary key and foreign keys in each table. Indicate one or two other
fields in each table (you need not list them all).
You have been hired as a database consultant to the local video store
that rents and/or sells tapes to customers. The store maintains the
usual information about every customer (name, address, phone number, and
so on). It also has detailed information about every movie, such
as its duration, rating, rental price, and purchase price. One customer
can rent several tapes and the same tape will (over time) be rented to
many customers.
The owner of the store needs a detailed record of every rental that
identifies the movie, the customer, the date the rental was made, and the
number of days the customer may keep the movie without penalty.
Class scheduling represents a major undertaking at any university.
It entails the coordination of course offerings as published in a registration
schedule together with faculty assignments. All courses have a formal
title but are more commonly known by a six position course-ID. Microcomputer
Applications, for example, is better known as CIS120. The first three
characters in the course-ID denote the department (e.g., CIS stands for
Computer Information Systems). The last three
indicate the particular course.
The university may offer multiple sections of any given course at different
times. CIS120, for example, is offered at four different times: at
9:00, 10:00, 11:00, and 12:00, with all sections meeting three days a week
(Mondays, Wednesdays, and Fridays). The information about when a
class meets is summarized in the one letter section designation; for example,
section A meets from 9:00 to 9:50 on Mondays, Wednesdays, and Fridays.
The published schedule should list every section of every course together
with the days, times, and room assignments. It should also display
the complete course title, number of credits, and the name of the faculty
member assigned to that section. It should be able to list all classes
taught by a particular faculty member or all sections of a particular course.
Design a relational database to satisfy these requirements.