There is an xyz company, which purchases some parts from vendors to produce some products. it has several departments, marketing sites, and parts supply vendors in the company
1) for each department, department id and department name will be recorded.
2) people in the company can be divided into three types -- employees, customers, and potential employees. each person can belong to more than one type. each person in the company has the following attributes: personal id, name (last name, first name), age (below 65), gender, address (address line 1, address line 2, city, state, zipcode), and phone number (one individual may have more than one phone number). for customers, his/her preferred salesmen were recorded in the system. for employees, rank and title (e. g. ceo, principle, partner, etc.) will be recorded for them.
3) each employee of the company must have only one direct supervisor, while each supervisor can have several supervisees. one employee can work for one or more departments at different time. but at one time, one employee can only work for one department. the system needs to record start time and end time for each shift among different department for one employee.
4) each job position's information is recorded to hire new people. it contains the job id, job description, and posted date in the system.
5) the job positions are posted by the departments. both existing employees and potential employees can apply each job post by any department. the company will select some candidates from the applications and make interviews.
6) for each job position, several interviews will be made to select a suitable person.
7) for each interview, candidates (interviewees), interviewers, job position and interview time are recorded. after each round interview, the interviewers give a grade to it ranging from 0 to 100. the grade over 60 represents that the interviewee pass the interview. one person is selected when her/his average grade is over 70 and she/he passes at least 5 rounds of interviews.
8) for each product in the company, the system needs to record product id, product type, size, list price, weight, and style.
9) there are many marketing sites for the company. for each site, site id, site name, and site location are recorded.
10) there are several people working for each site, and meanwhile, one person can work on different sites. it is able to track the details of each sale history -- salesmen, customers, product, sale time, and sites.
11) part purchase is also a vital activity in the company. the system needs to record each vendor's vendor id, name, address, account number, credit rating, and purchasing web service url.
12) one vendor may supply many types of parts. the price of the same part type may vary from different vendors but the price of one part type of one vendor will keep same. it is able to track which part types used in each product and the number of each type of part used for the product.
13) in addition, the system maintains the information of each employee's monthly salary which includes transaction number, pay_date, and amount (note: transaction number could be same among different employees. however, for each employee, the transaction number is unique).