SMART Construction Company

Question: Case Study SMART Construction CompanySMART 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.Question 1:(a) Identify a single un-normalized relation for the above scenario.(b) Identify the primary key of the un-normalized relation (c) Identify all functional dependencies available in the un-normalized relation(d) Using the functional dependencies identified in Q4.c, produce BCNF relationsQuestion 2:Evaluate, compare and contrast the two methods of relational database design, namely top down method and bottom up method. What will be your choice if you are asked to select a method? Justify your choice. Answers: SMART Construction Company 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 Location: Contact telephone number: Branch Name: Job Number: Date of Completion: Minimum Quoted amount: Contractor name# Contractor Address 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). Solution: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 norm al 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 Solution: 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 eva luate, 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. References: 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. 377387, June 1970.