SMART Construction Company has four branches. A unique branch number, location and a contact telephone number identify each branch. The company is interested in developing a computer-based information system to maintain records on selecting contractors, allotting the job to the contractors and supervising the progress of a job. The company has many departments to do the above tasks. Each department is identified by a unique number, its name, Telephone number and the floor in which it is located.
The Tender department of SMART invites quotations to build buildings from contractors. Each tender is given a unique tender number and a deadline for submission. Each Contractor is given a unique membership number. Contractor data include name and address. Contractors submit quotations. Each quotation includes a number, date of submission and total amount quoted. Each Contactor can submit only one quotation for one tender. For each tender there may be many quotations submitted by many contractors.
The job allotment department allots jobs to the contractors. A job is given a job number, date of commencement of the job, date of commencement of the job and date of completion of the job. A job is assigned to only one contactor who had quoted the minimum amount for a tender number.
(a) Identify a single un-normalized relation for the above scenario.
The table consists of a repeating group or multiple entries for a single record is called as the un- normalized relation. Here, we have to identify a single un-normalized relation for the scenario given in the above case. In single un-normalized relation, we only consider the single tender with multiple entries of quotations from many contractors. For example the scenario is given below:
Branch Number: 0001
Date of Completion:
Minimum Quoted amount:
(b) Identify the primary key of the un-normalized relation
The primary key of the un-normalized relation in the above case is given as the job number, minimum quoted amount and completion date. These factors are important for the tender of buildings.
(c) Identify all functional dependencies available in the un-normalized relation identified in Q4 (a).
For this part, we have to identify all functional dependencies available in the un-normalized relation for given case study of tender of buildings. Let us see all these functional dependencies given below:
1) Un-normalized form: The table consists of a repeating group or multiple entries for a single record is called as the un- normalized relation. Here, we have to identify a single un-normalized relation for the scenario given in the above case. In single un-normalized relation, we only consider the single tender with multiple entries of quotations from many contractors.
2) First Normal Form (1NF): We know that in the first normal form, values in the domain of each attribute are atomic or we can say that each row of table must have single value. The condition is that no two rows are identical. This means, one job should be allocated to only one contractor. If there is a selection of single contractor for the single job, then there wills first normal form exists and each job in the table appears only one times.
3) Second Normal Form (2NF): In this form, there is no any partial dependencies exist. For this case, if there is no any partial dependencies exists between the contractor and job or quoted amount, then this form will be consider as second normal form. Sometimes, there will be some partial dependencies between the job, branch, quoted amount, contractor, etc.
4) Third Normal Form (3NF): In this form, there is no any transitive dependency or we can say indirect dependency exists. For this case, if there is no any transitive dependency exists between the job number, contractor, quoted amount, etc, then we can say that third normal form exists for this case. Sometimes, contractor or quoted amount responsible for the indirect dependency for allocation of the tenders for buildings.
(d) Using the functional dependencies identified in Q4.c, produce BCNF relations
Boyce – Codd Normal Form (BCNF): We say that the relation is in the BCNF if the every determinant for all possibilities is a candidate key. For this case, if we consider all the determinants with all rows represent the contractor in terms of different factors, then we can say that the relation is in BCNF. This mean, all the tables represents all candidates regarding all the information about quoted amount, completion date, job number, etc.
2. Evaluate, compare and contrast the two methods of relational database design, namely top down method (used in Question 1) and bottom up method (used in Question 4). What will be your choice if you are asked to select a method? Justify your choice.
For this question, we have to evaluate, compare and contrast the two methods of the relational database design for the case of the tender of buildings. Let us see these two methods for the relational database design given below:
One to Many: In this design, the table is consisting of one job and many contractors. The data for the table is constructed according to the first job with many contractors with other information. This table is suitable for the data access for the particular job and related contractors and bids.
Many to One: In this design, table is consisting of many jobs and single contractor. For this scenario, we construct the table according to the contractor and the jobs contractor applied for. In case of data access for the particular contractor and related jobs, this design is useful.
If we compare both above methods, first method is more suitable because we many times need the information regarding the particular jobs instead of interested contractor for the tender of buildings.
• Paul Litwin, "Fundamentals of Relational Database Design", available at https://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx.
• Codd E. F., "A Relational Model of Data for Large Shared Data Banks", Communications of the ACM, vol. 13, issue 6, pp. 377–387, June 1970.
Getting nightmare regarding pending chemistry assignment? Then it is high time you consider hiring chemistry assignment help service of MyAssigemnthelp.com. We at MyAssignmenthelp.com deliver quality subject specific assistance on more than 100+ subjects. For us, students' need is foremost, and we work round the clock to offer science, humanities and commerce assignment help. Among Commerce wing, our one of the most popular services is capital budgeting assignment help. In science segment, we have gained popularity with biology assignment help.
You are required to write a researched argument essay that convinces persuades the reader of your position / stance. This is an academic, researched and referenced do...Read More
Executive Summary The purpose of this report is to elaborate the factors which are considered by individuals before selecting an occupation. Choosing an occupati...Read More
Introduction With the increase enhancement in the field of technology, it has been considered essential by the businesses to implement such technology in their b...Read More
Executive Summary In a merger & acquisition, role of an HR has emerged as a very critical function. At each stage of merger and acquisition process, HR plays a s...Read More
Introduction In this competitive business environment where every business organization is trying to attract the customers of each other, it becomes essential for ...Read More