Refer to the previous homework assignment for definitions of what is meant by a database design outline and an entity-relationship diagram.
In each
question below, assume that any "address information" is in U.S. format, consisting of
fields for
street address, city, state code, and zip code. For both problems,
assume that you do not have access to a master zip code table. You may create additional
"ID" or account number fields where necessary.
Q1. Career placement office: you need to set
up a database for your college's career placement office. For each
student, you want to store the student ID number, first name, middle
name/initial, last name, address information, cell phone number, e-mail, and
expected graduation date. You also want to keep information on employers,
including employer name, address information, and main phone number. Each
employer has one or more recruiters; each recruiter only works for only one
employer. For each recruiter, you want to store a first name, middle
name/initial, last name, address information, office phone number, cell phone
number, and e-mail. Your office has a facility in which recruiters and
students can meet for preliminary job interviews, and you want your system to
keep track of the schedule for these interviews. Each interview consists
of a single recruiter meeting a single student. For each interview, you
want to store the date/time scheduled to start, date/time actually started
(blank for interviews in the future), date/time scheduled to end, date/time
actually ended (blank for interviews that have not been held yet), room number,
and comments. Design a database to hold
all this information. Hand in a database design outline and an ER diagram.
Q2. Stockbroker's office: You are setting up a system to track trades and portfolios for a small stockbroker's office. For each broker in the office, you want to store a first name, middle name/initial, last name, phone extension number, cell phone number, e-mail address, and date hired. For each of the firm's clients, you want to store a first name, middle name/initial, last name, address information, home phone number, work phone number, cell phone number, and e-mail address. Each client is assigned to a single stockbroker, and the database should remember which one. You also have a master list of securities which your firm is licensed to trade. For each security, you want to store a ticker symbol and a description. No two securities may have the same ticker symbol. Finally, you want to keep track of each client's transactions. For each transaction, you want to store the date/time it occurred, a transaction type ("Buy", "Sell", "Dividend", etc.), which security was involved, the net number of shares, and the net cash. For example, (ignoring commissions) buying 100 shares of IBM at $98.55 per share would mean a transaction type of "Buy", net number of shares = +100, and net cash = –$9,855.00, while selling 10 shares of Cisco Systems (ticker symbol CSCO) at $27.21 per share would mean a transaction type of "Sell", net shares = –10, and net cash = +$272.10.