Show tables you would design for the following situation. Your design should avo

Question

Show tables you would design for the following situation. Your design should avoid modifications problems and implement relations. indicate the atrributes in the tables that would uniquely identify the row( ie primary key), document any assumptions you make that would affect the way you woud have designed your table.

The stuff corporation is a company with approx 500 employees that produces stuff. A database is required to keep track of all employees, departments and vendors. Obviously, an employees name, adress, office phone number and salary is needed in the database. the following additional business policies for the stuff corporation exist:

1.Employees are either engineers or administrative staff, but not both

2.An employee must have a degree in engineering to be considered an engineer.

3. it is important to record the universities that engineers have attended, what kinds of engineering degrees those universities offer, and what kind of degree our engineer has. All degrees earned by an engineer should be recorded in the database

4. The stuff corporation is divided into departments: accounting, research and development and marketing. An employee can be reported to only one department. Each department has several employees but only one manager. Managers are classified as "managerial" administrative staff. It is important to know the name and phone number of each departmetns manager.

5. Each department is responsible for purchasing its equipment and supplies from vendors. Stuff corporation has to pay for these things so the vendor's name and adress is needed. A record of every purchase (item, cost, and date of purchas) is needed. A vendor may supply equipment or supply to many departments. It is not necessary to know everything a vendor sells, only the things purchased and which department purchased them is needed.

Explanation / Answer

Show tables you would design for the following situation. Your design should avoid modifications problems and implement relations. Indicate the attributes in the tables that would uniquely identify the row ( i.e. primary key), document any assumptions you make that would affect the way you would have designed your table.

The stuff corporation is a company with approx. 500 employees that produces stuff. A database is required to keep track of all employees, departments and vendors. Obviously, an employee’s name, address, office phone number and salary is needed in the database. The following additional business policies for the stuff corporation exist:

1. Employees are either engineers or administrative staff, but not both

2. An employee must have a degree in engineering to be considered an engineer.

3. It is important to record the universities that engineers have attended, what kinds of engineering degrees those universities offer, and what kind of degree our engineer has. All degrees earned by an engineer should be recorded in the database

4. The stuff corporation is divided into departments: accounting, research and development and marketing. An employee can be reported to only one department. Each department has several employees but only one manager. Managers are classified as "managerial" administrative staff. It is important to know the name and phone number of each department’s manager.

5. Each department is responsible for purchasing its equipment and supplies from vendors. Stuff Corporation has to pay for these things so the vendor's name and address is needed. A record of every purchase (item, cost, and date of purchase) is needed. A vendor may supply equipment or supply to many departments. It is not necessary to know everything a vendor sells, only the things purchased and which department purchased them is needed.

As there is a requirement to maintain all the records pertaining to all the staff (approx. 500 in number) along with the details of suppliers and procurement from done these suppliers. I would suggest to prepare following tables in the database to easily maintain and manage the records.

At the very high level there would be a classification of tables into two categories

Basic Data tables: Basic data tables would contain the data pertaining to transactions which is kind of constant in nature and will be used again and again in transactions and there will be very less modifications in this data set. Also the frequency of addition of data would be relatively less compared to the transactional data.

Transaction data tables: These tables would contain the transactional details along with all the data required for statutory / legal requirements of the country & company. For instance tax related information along with the date and time of transaction carried out would also be present in these tables.

Basic Data Table could be as follows

Organization Basic data: containing details of company, departments, various roles present in the organization along with the organization hierarchy or the organization tree, work calendar, shift timings etc. Primary key could be the unique identification code for department, unique code for every role present within the department for instance code M001 for managers, S001 for supervisors, etc.

Educational institute’s basic data: - This table would contain details of all the listed educational institutes along with the list of courses offered. Here primary key would be unique code for the course / degree offered along with the unique identifier for the institute itself. For instance Engineering course in mechanical engineering could be ENG-MEC001 and similar unique codes can be assigned to different courses.

Supplier Master Data table: - This table would contain the details of suppliers for e.g. their names their addresses (delivery, shipment, billing, invoice, visit address etc.) along with other details like their bank account details for payment purposes, applicable taxes, contact information, payment terms, mode of payment acceptable etc. The primary key would be unique identifier for the supplier, unique identifier for the details of address, payment, bank account etc.

Parts master data table: This table would contain list of parts / services to be procured by various departments, along with unit of measure and similar details. Part number could be the unique / primary key.

Financial Basic data table: This set of tables would contain all the basic and master data related to financial transactions and Tax Legal Statutory requirements (TLS) for e.g. prevailing taxed and rates, VAT, GST related details, Chart of accounts etc. etc. Unique key for every item to be defined as per the code and applicability.

Transactional data tables

Employee Data Table :- This table would contain all the details of the employees like their joining date (date of leaving / retiring ), department which they joined, roles / position which they joined the organization, their contact information, their personal information like education details, marital status, details like passport numbers etc.. It would also contain details like contract details number of hours they are supposed to work, work calendar, shift timings, reporting manger / line manager etc.. It would also contain details of their salary.

Employee ID could be the unique key / primary key which will be used to store all the details information relation to employee.

Procurement data table: - This table would contain details related to items being procured by respective suppliers along with the rates (unit price, gross price, tax details etc.) along with the historical information about the same part being procured by respective departments.

All the checks and rules to be defined in these tables so that while data entry errors can be avoided for e.g. same employee should not be assigned to two departments, role engineer cannot be assigned to an employee until his education details have engineering degree assigned against him in the basic tables.

Procurement data table should also capture details of department while procuring the items for e.g. office supplies being procured for department 1 and department 2 along with respective quantity should be present in the table.

All the unique items like Employee ID, Supplier ID, Part number etc. should be defined as the primary key in the tables

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.