Database Design & Development- Report

11 Pages1478 Words299 Views

Added on 2019-11-26

Database Design & Development- Report

Added on 2019-11-26

BookmarkRelated Documents

Cars Database1Database Design & DevelopmentSubmitted ByCourseProfessorDate

Cars Database2NormalizationNormalization is the process of minimizing redundancy as well as dependency of data in adatabase design. (https://www.guru99.com, 2017). Third Normal Form (3NF) depends on theidea of transitive reliance. A utilitarian reliance X → Y in a connection blueprint R is a transitivereliance if there exists an arrangement of traits Z in R that is neither an applicant key nor a subsetof any key of R, and both X → Z and Z → Y hold. As indicated by Codd's unique definition, aconnection mapping R is in 3NF and also it fulfills 2NF and no nonprime quality of R istransitively indigent on the primary key. (Chavan, S., B. & Dr. Meshram, B., B., 2012).Entities1. Employee EmployeeID, emp_name, emp_address, emp_phone, emp_type.2. SalesPerson EmpID, emp_name, experienceForeign key  EmpID references Employee3. Admin EmpID, qualificationForeign key  EmpID references Employee4. Mechanic EmpID, certificate_levelForeign key  EmpID references Employee5. Part_type PartID, Part_desc, price_per_unit6. Client ClientID, client_name, client_address, client_phone, senior_citizen.7. Cars RegNobr, make, model, color, produced_year, price, warranty_date,purchased_from_ASS, EmpID, ClientID.

Cars Database3Foreign key  EmpID references SalesPerson, ClientID references client8. Complaint ComplaintID, reason, lodged_date, outcome, ClientID, regnobr.Foreign key  ClientID references client, regnobr references cars.9. Service JobNbr, Job_desc, service_date, total_cost, complaintid, EmpID.Foreign key  EmpID references Mechanic, complaintid references complaint10. Part_used JobNbr, PartID, qty_used, part_cost, regnobr.Foreign key  Regnobr references cars.Functional DependenciesFunctional dependency is the set of relationship between two attributes. These are defined asthe imperative metadata which is used for performing normalization process, to purge the dataand in addition many other tasks. To fulfill all these requirements is the main challenge fordatabase analysis. Various methodologies can be used to normalize an unnormalized data. Inorder to implement normalization process, it is important to check attributes of an entitywhether any dependency exists in them or not. In the given scenario, we will first check thedependency in these attributes by checking their key values. In case, if these attributes satisfythe conditions of functional dependency, the database can be easily normalized. FD alsodescribes Boyce Codd Formula. The main advantage of performing functional dependencystep is to remove the repetition of data. It is mainly candidate key which helps to representingvalues of the attributes. The primary key that exists in the database ensures that information isunique and not null whereas the foreign key is the key that mainly ensures that data can bepredicted. As in the given scenario, we make multiple tables according to the requirement, few

Cars Database4of them are master tables whereas others are child tables that refers their master tables throughprimary key. (Kulkarni, A., Batule, S., 2016).In the given scenario, let’s take the relationship of cars and clients. In cars table, RegNobr isunique for each car and it is dependent on Make and Model. RegNobr  make, model. In caseof Client table, ClientID is dependent on Client_name like ClientID  Client_name. This showsthe functional dependency between different attributes of table cars and client. For being in third normal form (3NF), there must be a relationship between cars and client tablein order to reduce redundancy and dependency of the attributes. For making the tables in 3NF, Ihave used the concept that each client purchases a car from the showroom. The number of carspurchased can be more than one. So these tables will be interlinked by using a foreign key.Client will be master table and cars will be child table. It will be one to many relationship. TableCars will be linked with column ClientID. Every time when a client purchases a car, therespective ClientID will also be saved in database. SQL QueriesWhich services didn’t require the replacement of any parts? List those service details thatinclude job number, job description, service date, total cost and the corresponding car’sregistration number, make, model and produced year. SELECT service.JobNbr, service.Job_desc, service.service_date, service.total_cost, cars.RegNobr, cars.Make, cars.Model, cars.Color, cars.Purchased_yearFROM service, complaint, carsWHERE (((service.Job_desc)='No Part Required') AND ((service.complaintid)=[complaint].[complaintid]) AND ((complaint.RegNobr)=[cars].[RegNobr]));

End of preview

Want to access all the pages? Upload your documents or become a member.

Assignment on The Database System

|739

|52

Database System Assignment docx

|566

|98

Data Model, Normalisation, DDL and DML for Spock Bank Database

|14

|1412

|148

COIT20247- Database Design and Development

|699

|75

ITECH 1006 Database Management Report Assignment

|1051

|179

Report On Implementation Of AAS Database

|474

|35

Database Design & Development- Report