Database Design and Development Interior Design

Published:

This assignment will allow you to demonstrate an understanding of applying data modeling and data analysis techniques to the design and development of database solutions.

The Scenario

You have been sub - contracted to design the database system for Marlowe Interiors. Marlowe Interiors is a medium sized interior design company that does building work and decorating. An initial analysis of Marlowe Interiors has identified the following requirements:

Marlowe Interiors keeps a record of the jobs it performs. Jobs are for particular customers and classified by job type (Single Room, Part - house, Whole - house). The full cost of a job depends on the labour used and the parts used. Records of parts and labour should be kept separately.

Labour costs depend on the type of work done. A job may have different sorts of work and employ one or more types of worker (plumber, labourer, qualified builder, interior designer, electrician). A worker will work on more than one job. The hourly rate of pay for each of these types of worker will vary as shown below:

Lady using a tablet
Lady using a tablet

Professional

Essay Writers

Lady Using Tablet

Get your grade
or your money back

using our Essay Writing Service!

Essay Writing Service

Type

Rate per Hour

Plumber

50

Labourer

20

Qualified Builder

55

Interior Designer

39

Electrician

50

A job may involve one or more parts. Examples of parts are: sink, bath, pipe, thermostat, wallpaper, decking, door, light-fitting. The costs of these parts will vary and suitable test values should be supplied as part of the initial implementation.

Parts are supplied by various suppliers. The cost of a part could vary depending on the supplier.

A customer record should be kept for the job with all the usual details such as name, address and phone-number.

Study the above detail and then perform the following tasks:

Task 1 - 30 Marks

Draw an Entity Relationship Data Model that describes the content and structure of the data held by Marlowe Interiors.

Customer

Job

Labour

Labour Type

Supplier

Supplied

Parts

Job Type

R111

R211

R311

R4311

R5

R6

R7

Fig 1-1 Entity Relationship Data Model

Representing Entities

Entity: Customer

Description: Person who add job at the Marlowe interiors.

Identifying Attribute(s): CuID

Other Attributes: Name, Add, Phone

Entity: Job

Description: Job which record the perform by Marlowe interiors.

Identifying Attribute(s): JoID,CuID, LaID

Other Attributes: Type, Work Hour

Entity: Job Type

Description: Job of type which are classified for particular customer

Identifying Attribute(s): JID, JoID, PaID

Other Attributes: Type

Entity: Labour

Description: Person who work at the job.

Identifying Attribute(s): LaID

Other Attributes: LaName, RateperHour

Entity: Labour Type

Description: Type of worker which are classified by the Marlowe Interiors.

Identifying Attribute(s): LID, LaID

Other Attributes: Type

Entity: Parts

Description: Parts which are involve in the Marlowe Interiors.

Identifying Attribute(s): PaID

Other Attributes: Type, Cost,Name

Entity: Supplied

Description: Supplied which are involves various suppliers.

Identifying Attribute(s): SID, PaID, SuID

Other Attributes: Price

Entity: Suppliers

Description: Person who supply various parts.

Identifying Attribute(s): SuID

Other Attributes: Name, Add, Phone

Representing Relationships

Relationship ID: R1

Relationship Name(s): add job, do for

Description: Relates the customer and job for add job

Participating Entity: Customer

Cardinality: one

Optionality: mandatory

Participating Entity: Job

Cardinality: many

Optionality: mandatory

Relationship ID: R2

Relationship Name(s): is Type of, Type of

Description: Relates job which involve various job type

Participating Entity: Job

Cardinality: one

Optionality: mandatory

Participating Entity: Job Type

Cardinality: many

Optionality: mandatory

Relationship ID: R3

Relationship Name(s): working, is working

Description: Relates labour who work at the job

Participating Entity: Job

Cardinality: many

Optionality: mandatory

Participating Entity: Labour

Cardinality: one

Lady using a tablet
Lady using a tablet

Comprehensive

Writing Services

Lady Using Tablet

Plagiarism-free
Always on Time

Marked to Standard

Order Now

Optionality: mandatory

Relationship ID: R4

Relationship Name(s): Type of job, which involve in job

Description: Relates job type may involve one or more parts.

Participating Entity: Job Type

Cardinality: many

Optionality: mandatory

Participating Entity: Parts

Cardinality: one

Optionality: mandatory

Relationship ID: R5

Relationship Name(s): is working, type of worker

Description: Relates Labour which involves various labour types.

Participating Entity: Labour

Cardinality: one

Optionality: mandatory

Participating Entity: Labour Type

Cardinality: many

Optionality: mandatory

Relationship ID: R6

Relationship Name(s): which involve in job, supplied the parts

Description: Relates supplied as part of the initial implementation.

Participating Entity: Parts

Cardinality: one

Optionality: mandatory

Participating Entity: Supplied

Cardinality: many

Optionality: mandatory

Relationship ID: R7

Relationship Name(s): supplied the parts , for supply the parts

Description: Relates suppliers are supplied for parts.

Participating Entity: Supplied

Cardinality: many

Optionality: mandatory

Participating Entity: Supplier

Cardinality: one

Optionality: mandatory

Presenting Attributes

Attribute: CuID

Description: The ID number of customer

Type: character {1, 2, 3, 4, 5}

Attribute: Name

Description: The name of customer

Type: character {Ko Myo, Kyaw Kyaw, Daw Hla, U Win, Ei Ei}

Attribute: Add

Description: The address of the customer

Type: character {Mandalay, Yangon, Mongywa, Yangon, Mandalay}

Attribute: Phon

Description: The phone number of customer

Type: character {02-67854, 09-5400564, 09-2132167, 01-757889, 09-2003678}

Attribute: JoID

Description: The ID number of the Job

Type: character {1, 2, 3, 4, 5}

Attribute: Work Hour

Description: The work hour of the worker

Type: character {8, 20, 12, 10, 15}

Attribute: JID

Description: The ID number of the job type

Type: character {1, 2, 3}

Attribute: Type

Description: Type of the job

Type: character {Single Room, Part-house, Whole-house}

Attribute: LaID

Description: The ID number of the labour

Type: character {1, 2, 3, 4, 5}

Attribute: Name

Description: The name of the labour type

Type: character {Plumber, Labourer, Qualified Builder, Interior Designer, Electrician}

Attribute: Rateperhour

Description: The price of the labour in one hour

Type: character {50, 20, 55, 39, 50}

Attribute: LID

Description: The ID number of the labour type

Type: character {1, 2, 3, 4, 5}

Attribute: Type

Description: Type of the labour

Type: character {Labourer, Interior Designer, Plumber, Qualified Builder, Electrician}

Attribute: PaID

Description: The ID number of the parts

Type: character {1, 2, 3, 4, 5, 6, 7, 8}

Attribute: Type

Description: Type of the parts

Type: character {sink, bath, pipe, themostar, wallpaper, decking, door, light-fitting}

Attribute: Cost

Description: The price of the parts

Type: character {45, 55, 25, 40, 30, 50, 25, 30}

Attribute: SID

Description: The ID number of the supplied

Type: character {1, 2, 3, 4, 5}

Attribute: Price

Description: The cost for supply

Type: character {10, 9, 8, 15, 10}

Attribute: SuID

Description: The ID number of the supplier

Type: character {1, 2, 3, 4, 5}

Attribute: Name

Description: The name of the supplier

Type: character {Min Min, Zaw Zaw Ag, Mg Mg, Ag Khant, Kyaw Kyaw}

Attribute: Add

Description: The address of the supplier

Type: character {Mandalay, Mongywa, Mandalay, Yangon, Yangon}

Attribute: Phon

Description: The phone number of the supplier

Type: character {02-68662, 09-2135759, 09-5767865, 01-657773, 01-587988}

Task 2- 20 Marks

Produce the resulting tables clearly indicating the primary and foreign keys.

Customer

Field Name

Data Type

Key

CuID

Integer

Primary Key

Name

Varchar (25)

Add

Varchar (25)

Phone

Varchar (10)

Job

Field Name

Data Type

Key

JoID

Integer

Primary Key

CuID

Integer

Foreign Key

LaID

Integer

Foreign Key

Work Hour

Decimal (10)

Labour

Field Name

Data Type

Key

LaID

Integer

Primary Key

LaName

Varchar (25)

Rateperhour

Integer

Labour Type

Field Name

Data Type

Key

LID

Integer

Primary Key

Type

Varchar (45)

LaID

Integer

Foreign Key

Job Type

Field Name

Data Type

Key

Lady using a tablet
Lady using a tablet

This Essay is

a Student's Work

Lady Using Tablet

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

Examples of our work

JID

Integer

Primary Key

Type

Varchar (25)

JoID

Integer

Foreign Key

PaID

Integer

Foreign Key

Parts

Field Name

Data Type

Key

PaID

Integer

Primary Key

Type

Varchar (45)

Cost

Integer

Supplier

Field Name

Data Type

Key

SuID

Integer

Primary Key

Name

Varchar (25)

Add

Varchar (20)

Phon

Varchar (15)

Supplied

Field Name

Data Type

Key

SID

Integer

Primary Key

Price

Integer

Primary Key

PID

Integer

Primary Key

SuID

Integer

Task 3 - 20 Marks

Using a Database Management System (DBMS) of your choice, set-up all of the above normalised tables, and populate them with well-designed test data (minimum 5 records per table). Provide printouts of all tables.

Reasonable assumptions may be made with regard to data.

CREATE SCHEMA IF NOT EXISTS `InteriorDesign` ;

USE `InteriorDesign` ;

Customer

CREATE TABLE IF NOT EXISTS `InteriorDesign`.`Customer` (

`CuID` INT NOT NULL ,

`Name` VARCHAR(25) NULL ,

`Add` VARCHAR(25) NULL ,

`Phone` VARCHAR(10) NULL ,

PRIMARY KEY (`CuID`) )

Job

Table `InteriorDesign`.`Job`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `InteriorDesign`.`Job` (

`JoID` INT NOT NULL ,

`CuID` INT NULL ,

`LaID` INT NULL ,

`WorkHour` DECIMAL(10) NULL ,

PRIMARY KEY (`JoID`) ,

INDEX `CuID` (`CuID` ASC) ,

INDEX `LaID` (`LaID` ASC) ,

CONSTRAINT `CuID`

FOREIGN KEY (`CuID` )

REFERENCES `InteriorDesign`.`Customer` (`CuID` )

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `LaID`

FOREIGN KEY (`LaID` )

REFERENCES `InteriorDesign`.`Labour` (`LaID` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

Job Type

Table `InteriorDesign`.`Job Type`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `InteriorDesign`.`Job Type` (

`JID` INT NOT NULL ,

`Type` VARCHAR(45) NULL ,

`JoID` INT NULL ,

`PaID` INT NULL ,

PRIMARY KEY (`JID`) ,

INDEX `JoID` (`JoID` ASC) ,

INDEX `PaID` (`PaID` ASC) ,

CONSTRAINT `JoID`

FOREIGN KEY (`JoID` )

REFERENCES `InteriorDesign`.`Job` (`JoID` )

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `PaID`

FOREIGN KEY (`PaID` )

REFERENCES `InteriorDesign`.`Parts` (`PaID` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

Labour

Table `InteriorDesign`.`Labour`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `InteriorDesign`.`Labour` (

`LaID` INT NOT NULL ,

`LaName` VARCHAR(25) NULL ,

`Rateperhour` INT NULL ,

PRIMARY KEY (`LaID`) )

ENGINE = InnoDB;

Labour Type

Table `InteriorDesign`.`LabourType`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `InteriorDesign`.`LabourType` (

`LID` INT NOT NULL ,

`Type` VARCHAR(45) NULL ,

`LaID` INT NULL ,

PRIMARY KEY (`LID`) ,

INDEX `LaID` (`LaID` ASC) ,

CONSTRAINT `LaID`

FOREIGN KEY (`LaID` )

REFERENCES `InteriorDesign`.`Labour` (`LaID` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

Parts

Table `InteriorDesign`.`Parts`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `InteriorDesign`.`Parts` (

`PaID` INT NOT NULL ,

`Type` VARCHAR(45) NULL ,

`Name` VARCHAR(25) NULL ,

`Cost` INT NULL ,

PRIMARY KEY (`PaID`) )

ENGINE = InnoDB;

Supplied

Table `InteriorDesign`.`Supplied`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `InteriorDesign`.`Supplied` (

`SID` INT NOT NULL ,

`PaID` INT NULL ,

`SuID` INT NULL ,

`Price` INT NULL ,

PRIMARY KEY (`SID`) ,

CONSTRAINT `fk_Supplied_Parts1`

FOREIGN KEY (`SID` )

REFERENCES `InteriorDesign`.`Parts` (`PaID` )

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `fk_Supplied_Supplier1`

FOREIGN KEY (`SID` )

REFERENCES `InteriorDesign`.`Supplier` (`SuID` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

Supplier

-----------------------------------------------------

-- Table `InteriorDesign`.`Supplier`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `InteriorDesign`.`Supplier` (

`SuID` INT NOT NULL ,

`Name` VARCHAR(25) NULL ,

`Add` VARCHAR(20) NULL ,

`Phone` VARCHAR(15) NULL ,

PRIMARY KEY (`SuID`) )

ENGINE = InnoDB;

Task 4 - 20 Marks

Set-up and test all of the following queries using Structured Query Language (SQL). Provide printouts of SQL code for each query and the output produced when you run the query in the database you have developed.

Display the names and addresses of the customers of Marlowe Interiors and the jobs and job types that have been carried out for them.

Display a full record of a job. This should include the name of the customer, the job type, the workers on the jobs and what type they are, the parts included in the job and the final overall cost for the job.

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `jobforcustomer` AS select `customer`.`Name` AS `Name`,`customer`.`Add` AS `Add`,`job`.`JoID` AS `JoID`,`jobtype`.`Type` AS `Type`,`job`.`WorkHour` AS `WorkHour` from ((`customer` join `job` on((`customer`.`CuID` = `job`.`CuID`))) join `jobtype` on((`job`.`JoID` = `jobtype`.`JoID`)));

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `overallcost` AS select `customer`.`Name` AS `Name`,`customer`.`Add` AS `Add`,`job`.`JoID` AS `JoID`,`jobtype`.`JID` AS `JID`,`labour`.`Name` AS `LabourName`,`labour`.`Rateperhour` AS `Rateperhour`,`job`.`WorkHour` AS `WorkHour`,`labourtype`.`Type` AS `WorkerType`,`parts`.`Type` AS `Type`,`parts`.`Cost` AS `Cost`,((`labour`.`Rateperhour` * `job`.`WorkHour`) + `parts`.`Cost`) AS `TotalCost` from (`parts` join ((`labour` join ((`customer` join `job` on((`customer`.`CuID` = `job`.`CuID`))) join `jobtype` on((`job`.`JoID` = `jobtype`.`JoID`))) on((`labour`.`LaID` = `job`.`LaID`))) join `labourtype` on((`labour`.`LaID` = `labourtype`.`LaID`))) on((`parts`.`PaID` = `jobtype`.`PaID`)));

Task 5 - 10 Marks

Explain any assumptions you have made when analyzing, designing and implementing the above database, justify the approach you have taken and explain any alternative approaches you could have taken to any of the above tasks. Discuss any changes you would make to improve your work.

Assumption in analysing, designing and implementing

In this assignment the scenario is giving the complete information, details of required request system and gives point of the system. We assume that a data model is represented using the constructs of the E-R approach. In this approach, a given universe of discourse is represented using an entity model: a model built up of entities, relationship and attributes. An entity may be defined as a thing which an organization recognizes as being capable of an independent existence and which can be uniquely identified. An entity is an abstraction from the complexities of some domain. A relationship is some association between entities.

In this assignment, I design an Entity Relationship Data Model (ERD) and Data-Dictionary following the scenario. The relationship between customer and job is one of the any person who add job at the Marlowe Interiors, and the relationship between job and job type is, job which record the perform by Marlowe interiors, and job type is job of type which are classified for particular customer, and the relation ship between job and labour is one of any person who is working at the job. The relation ship between Job type and parts is the parts, which are involve in the job type, and the relation ship between labour and labour type, which the labour is involve various type of labour, and the relationship between the parts and supplied is the parts are supplied by the suppliers. The relation ship between supplied and supplier is the any person who supplies the various parts. And then, we make tables; it involves field name, data type and descriptions; that expressed the primary key and foreign key, and we make the steps by steps of normalisation tables. Using Structured Query Language (SQL) make question requested, it includes SQL code and output tables of print screen. At last, I make assumption in analyzing, designing and implementing, explain the alternative approaches and to improve my work. However, all tasks can be thinker widely and will be have in this system at drawing tables. Therefore, in this assignment can insert many assumptions and thinking and will be found in this system.

System analysis involves two primary and interrelated activities - requirements elicitation and requirements specification. Both sets of activities demand different techniques. Requirement elicitation demands approaches to identifying requirements. Requirements specification demands techniques for representing requirements. System analysis benefits from forms of stakeholder participation, especially in the elicitation phase.

The conceptual modeling is the analysis of data in the summary. In this situation, conceptual data modeling groups cover more than normalization. I have the results provide a need analysis. Then we defined unit and pooled data from requirements analysis for each player. In this situation, I have studied also the relationships and cardinality between the entities. In this Task 1, I showed ERT diagram of part of the system AHR.

Logical database design is the process of building an enterprise data model is a model of business data requirements for an organization part of it. Physical database design includes the results from the logical design process, the calibration exercise in the use of resources, efficiency and storage of certain applications and then some decisions on the implementation of an elected DBMS. The main exit logical database design process is the logical data model. In most projects cotemporary database design of this model will be expressed as a relational schema. Logical database design is the process of constructing a business data model.

It involves the reconciliation of the conceptual model to a relation schema.

It is implementation independent.

Normalization and production of a 3NF schema take place.

The cores of the theory of relational databases are functional dependencies and normalization. Standardization is a process that maintains the functional dependencies between attributes and decide which is the Internet and in relationships. The result is a design of logical database that is structurally stable and has minimal redundancy. But a design normalized database is not optimal processing efficiency. This leads to the notion renormalizations where by the relational schema is to improve by introducing redundancy in a controlled manner to the performance of the database fine.

A logical database model provides insufficient information to enable effective physical database design. Usually, the following activities need to be performed as part of physical design:

Volume analysis

Usage/ transaction analysis

Integrity analysis

Control/ security analysis

Distribution analysis

So as to achieve implementation of the system, I remember receiving regulatory context of normalization of AHR department physical construction of the model. In this phase, I analyzed volume, transaction integrity, and security and distribution control of the relationship. When I finished this physical model, I reached the implementation state.

If I change the next time here, I will change this to improve my work. Designed to access my database for each person. It has no authority. Next time I will add, designed to secure permission for the users of the AHR department for my database. I will do it again up for AHR department to reduce data loss. These changes, which I would do to improve my work in the future.

Alternative Approaches and Changes to Improve My System

During design stage, Entity analysis can be performed and resulting ERD can be reconciled with ERD from normalization. Performance can be increased by using Enterprise level RDBMS e.g. MS SQL, OracleInegrity can be better implemented by using procedural method such as PL/SQL

.Control/security can be introduced to the system.

When I work, I passed five tasks. In Task1, I produced Entity Relationship Data Mode. In Task2, I produced resulting tables clearly indicating the primary and foreign keys. In Task 3, I normalize these tables. In Task4, I set up normalization tables in my desired DBMS and add the test data. In Task 5, I described the states when I analyzed the interior design company database development system and I described the changes to improve my work.