Design The Database System For Marlowe Interiors Commerce Essay

Published:

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

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:

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.

TASK 1

Task 1 - 30 Marks

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

Task-1.1(Entity Relationship Diagram)

C:\Users\MaTheinWin\Desktop\Kyaw Zaw Lynn(DDD)\ddd.png

Figure (1.1): Entity Relationship Diagram for Interior Design Company

Task-1.2(Data Dictionary)

1.2.1 Entity

Entity : customer

Description : Any person who confer the job on Interior Design Company

Identifying (attributes) : cID

Other attributes : cPhNo, cName

Entity : jobtype

Description : Any kind of job that is accepted by the customer of Interior Design Company

Identifying (attributes) : jTypeID

Other attributes : jType, jHour, lID

Entity : labourer

Description : Any person on the job work in Interior Design Company

Identifying (attributes) : lID

Other attributes : jTypeID, jTypeID, lName

Entity : labourer type

Description : Any kind of person on the job work in Interior Design Company

Identifying (attributes) : lTypeID

Other attributes : lType, RatepHour

Entity : job

Description : Any job that is accepted by the customer of Interior Design Company

Identifying (attributes) : jID

Other attributes : pID, cID, jTypeID

Entity : part

Description : Any element that is used for doing the Job of Interior Design Company

Identifying (attributes) : pID

Other attributes : pType, pPrice, pImCost, partcol

Entity : supply

Description : Give something useful or necessary to the job of Interior Design

Company

Identifying (attributes) : sID, pID

Other attributes : -

Entity : supplier

Description : Any person who supplies parts for doing the job in Interior Design Company

Identifying (attributes) : sID

Other attributes : sName, sAddress, sPhNo

1.2.2 Relationship

Relationship ID: R1

Relationship Name: Give, Received

Description: Relation between with customer and job which it is performed with all usual details

Participating Entity: customer

Cardinality: one

Optionality: mandatory

Participating Entity: job

Cardinality: many

Optionality: mandatory

Relationship ID: R2

Relationship Name: Received, isReceived

Description: Relation between with job and jobtype which is the types of job

Participating Entity: job

Cardinality: many

Optionality: mandatory

Participating Entity: jobtype

Cardinality: one

Optionality: mandatory

Relationship ID: R3

Relationship Name: isReceived, Employ

Description: Relation between with jobtype and labourer who will work on the job

Participating Entity: jobtype

Cardinality: one

Optionality: mandatory

Participating Entity: labourer

Cardinality: many

Optionality: mandatory

Relationship ID: R4

Relationship Name: Employ, Employed

Description: Relation between with labourer and labourer type who will work on the job

Participating Entity: labourer

Cardinality: many

Optionality: mandatory

Participating Entity: labourertype

Cardinality: one

Optionality: mandatory

Relationship ID: R5

Relationship Name: Received, is used

Description: Relation between with job and part which is used for the job

Participating Entity: job

Cardinality: many

Optionality: mandatory

Participating Entity: part

Cardinality: one

Optionality: mandatory

Relationship ID: R6

Relationship Name: is used, issupplied

Description: Relation between with part which is supplied from the supplier and supply is used

Participating Entity: part

Cardinality: one

Optionality: mandatory

Participating Entity: supply

Cardinality: many

Optionality: mandatory

Relationship ID: R7

Relationship Name: issupplied, supply

Description: Relation between with supply and supplier who supply the parts for the job

Participating Entity: supply

Cardinality: many

Optionality: mandatory

Participating Entity: supplier

Cardinality: one

Optionality: optional

1.2.3 Attribute

Attribute: cID

Description: Customer's identification Number

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

Attribute: jID

Description: Job's identification Number

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

Attribute: jtypeID

Description: Identification Number of Job type

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

Attribute: lID

Description: Labour's identification Number

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

Attribute: lTypeID

Description: Identification Number of labour type

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

Attribute: pID

Description: Part's identification Number

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

Attribute: sID

Description: Supplier's identification Number

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

1.2.4(Integrity Constraints)

A Job may have different type of work and use One or more types of workers

A worker will work on more than one job.

A Job may involve one or more parts.

Jobs are for specific customers and Job types are classified into Single Room, Part-house and Whole-house.

Parts and labor records should be kept separately and overall cost will depend on the Parts used and the Labour used.

Records of customer should be kept for job with the details such as name, address and phone-number.

Types of worker are plumber, labourer, Qualified Builder, Interior Designer, Electrician

Parts are supplied by different supplier and the cost of the part vary depend on the different supplier.

TASK 2

Task 2 - 20 Marks

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

customer

The customer table is to store the information of the customer including customer ID, customer name, and the phone number.

No

Name

Description

Type

Key

1

cID

Customer's Identification Number

INTEGER

PK

2

cName

Name of the customer

VARCHAR(30)

_

4

cPhNo

Phone Number of the Customer

VARCHAR(20)

_

job

This table is to store the the Job Information including the pID, cID, jID and jTypeID.

No

Name

Description

Type

Key

1

pID

Part's Identification Number

INTEGER

FK

2

cID

Customer's Identification Number

INTEGER

FK

3

jID

Job's Identification Number

INTEGER

PK

4

jTypeID

Identification Number of job type

INTEGER

FK

jobType

This table is to store the job type information including the jHour, jType, jCost, lID and jTypeID.

No

Name

Description

Type

Key

1

jHour

Hour of job

INTEGER

-

2

lID

Labourer's Identification Number

INTEGER

-

3

jType

Type of Job

VARCHAR(30)

-

4

jTypeID

Identification Number of job type

INTEGER

PK

labourer

This table is to store the labourer information including the lID, jTypeID, lTypeID and lName.

No

Name

Description

Type

Key

1

lID

Labourer's Identification Number

INTEGER

PK

2

jTypeID

Identification Number of job type

INTEGER

FK

3

lTypeID

Identification Number of labourer type

INTEGER

FK

4

lName

Name of labourer

VARCHAR(45)

-

labourerType

This table is to store the type of the labourer's information including the lType, lTypeID and RatepHour.

No

Name

Description

Type

Key

1

lType

Labourer's Identification Number

VARCHAR(30)

-

2

RatepHour

Rate per hour

INTEGER

-

3

lTypeID

Identification Number of labourer type

INTEGER

PK

part

This table is to store the part information including the pID, pType, pPrice, pImCost and partcol.

No

Name

Description

Type

Key

1

pID

Part's Identification Number

INTEGER

PK

2

pType

Type of part

VARCHAR(30)

-

3

pPrice

Price of part

INTEGER

-

4

pImCost

Implementation cost of part

INTEGER

-

5

partcol

Colour of part

VARCHAR(45)

-

supply

This table is to store the supply information including the pID and sID.

No

Name

Description

Type

Key

1

pID

Part's Identification Number

INTEGER

PK

2

sID

Supply's Identification Number

INTEGER

PK

supplier

This table is to store the supplier's information including the sID, sName, sAddress and sPhNo.

No

Name

Description

Type

Key

1

sID

Supply's Identification Number

INTEGER

PK

2

sName

Name of supplier

VARCHAR(30)

-

3

sAddress

Address of supplier

VARCHAR(30)

-

4

sPhNo

Phone number of supplier

VARCHAR(20)

-

TASK 3

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.

Customer table

CREATE TABLE `customer` (

`cID` int(11) NOT NULL,

`cPhNo` varchar(20) DEFAULT NULL,

`cName` varchar(30) DEFAULT NULL,

PRIMARY KEY (`cID`),

UNIQUE KEY `cID_UNIQUE` (`cID`)

)

Figure (3.1); Customer table with testing data

Job table

CREATE TABLE `job` (

`pID` int(11) NOT NULL,

`cID` int(11) NOT NULL,

`jID` int(11) NOT NULL,

`jTypeID` int(11) NOT NULL,

PRIMARY KEY (`jID`)

)

Figure (3.2); Job table with testing data

Jobtype table

CREATE TABLE `jobtype` (

`jType` varchar(30) DEFAULT NULL,

`jHour` int(11) DEFAULT NULL,

`lID` int(11) DEFAULT NULL,

`jTypeID` int(11) NOT NULL DEFAULT '0',

PRIMARY KEY (`jTypeID`),

UNIQUE KEY `jTypeID_UNIQUE` (`jTypeID`)

)

Figure (3.3); Jobtype table with testing data

Labourer table

CREATE TABLE `labourer` (

`lID` int(11) NOT NULL,

`jTypeID` int(11) NOT NULL,

`lTypeID` int(11) NOT NULL,

`lName` varchar(45) NOT NULL,

PRIMARY KEY (`lID`)

)

Figure (3.4); Labourer table with testing data

Labourertype table

CREATE TABLE `labourertype` (

`lTypeID` int(11) NOT NULL,

`lType` varchar(30) DEFAULT NULL,

`RatepHour` int(11) DEFAULT '0',

PRIMARY KEY (`lTypeID`),

UNIQUE KEY `lTypeID_UNIQUE` (`lTypeID`)

)

Figure (3.5); Labourertype table with testing data

Part table

CREATE TABLE `part` (

`pID` int(11) NOT NULL,

`pType` varchar(30) DEFAULT NULL,

`pPrice` int(11) DEFAULT NULL,

`pImCost` int(11) DEFAULT NULL,

`partcol` varchar(45) DEFAULT NULL,

PRIMARY KEY (`pID`)

)

Figure (3.6); Part table with testing data

Supplier table

CREATE TABLE `supplier` (

`sID` int(11) NOT NULL,

`sName` varchar(30) DEFAULT NULL,

`sAddress` varchar(30) DEFAULT NULL,

`sPhNo` varchar(20) DEFAULT NULL,

PRIMARY KEY (`sID`)

)

Figure (3.7); Part table with testing data

Supply table

CREATE TABLE `supply` (

`sID` int(11) NOT NULL,

`pID` int(11) NOT NULL,

PRIMARY KEY (`sID`,`pID`)

)

Figure (3.8); Supply table with testing data

TASK 4

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.

(A)SQL for displaying the names and addresses of the customer and the jobs and job types that have been carried out for them.

# Host: localhost

# Database: marlowe interiors

# Table: 'jobforcustomer'

#

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `jobforcustomer` AS select `customer`.`cID` AS `cID`,`customer`.`cName` AS `cName`,`jobtype`.`jType` AS `jType` from (`jobtype` join (`customer` join `job` on((`customer`.`cID` = `job`.`cID`))) on((`jobtype`.`jTypeID` = `job`.`jTypeID`)));

Figure (4.1): Result table for TASK (4-A)

(B)SQL for displaying a full record of a job that 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

# Host: localhost

# Database: marlowe interiors

# Table: 'totaljobcost'

#

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `totaljobcost` AS select `job`.`jID` AS `jID`,`job`.`cID` AS `cID`,`customer`.`cName` AS `cName`,`part`.`pType` AS `pType`,`part`.`pPrice` AS `pPrice`,`part`.`pImCost` AS `pImCost`,`jobtype`.`jType` AS `jType`,`jobtype`.`jHour` AS `jHour`,`labourertype`.`RatepHour` AS `RatepHour`,`labourer`.`lName` AS `lName`,((`jobtype`.`jHour` * `labourertype`.`RatepHour`) + (`part`.`pImCost` + `part`.`pPrice`)) AS `TotalCost` from (`supplier` join ((`part` join (`labourertype` join ((`jobtype` join (`customer` join `job` on((`customer`.`cID` = `job`.`cID`))) on((`jobtype`.`jTypeID` = `job`.`jTypeID`))) join `labourer` on((`jobtype`.`jTypeID` = `labourer`.`jTypeID`))) on((`labourertype`.`lTypeID` = `labourer`.`lTypeID`))) on((`part`.`pID` = `job`.`pID`))) join `supply` on((`part`.`pID` = `supply`.`pID`))) on((`supplier`.`sID` = `supply`.`sID`)));

Figure (4.2): Result table for TASK (4-B)

Task 5

Analyze on Database Management System being used

The main outputs from physical database design are:

-Volume analysis

-Usage/transaction analysis

-Integrity analysis

-Control/security analysis

-Distribution analysis

Database implementation involves the following activities:

Creating the physical schema,

Establishing storage structures and associated access mechanisms

Adding indexes, conduction de-normalization

Where appropriate

Exploiting the facilities of the chosen DBMS and implementing integrity constraints

Creating the physical schema involves implementation the data structures in the chosen database language.

Storage structures for the database objects ate established using the options of the chosen DBMS to meet the needs of usage.

Indexes are used to improve retrieval performance and will be guided by the analysis of access requirements.

De-normalization of the physical schema may be undertaken to improve performance.

De-normalization should be used with care.

Knowing about the facilities of the chosen DBMS and how to use these to fine-tune the database is important

Appropriate ways are chosen from the following to implement inherent and additional integrity constraints: inherently, procedurally of non-procedurally.

Database Management System is used here is MySQL Control Center 0.9.4. If MySQL is an open source language, the cost of using the system is free. Also, the RDBMS system and can be extended to enterprise level database system.

When I got the database design developed for the Interior Design Company, I have to do these four phases. They are requirement analysis, system analysis and system design and implementation stages. The requirement analysis stage I gather the necessary data for the necessity of the requirement elicitation. In the system analysis phase, I use the conceptual model. In the system design, I use logical model. In the implementation, use the physical model.

One of the main advantages of using MS SQl is the stored procedures. Stored procedure is the lines of code which the application calls and it is store on the server to compile into a pre-fast response time.

Tranction log is a record in the object to retrieve, update and delete records. Two reasons of using transaction logs is ther rollback of the procedure and the transaction is recorded for the security purpose. Rollback procedure is used in the accedental updates and delections which the database administrator using the transaction log back to the original data record can be returned. If a breach of security was suspects by the administrator, he can searches the transaction data of all types of logs which can be viewed to determine the severity of violations.

Changes to be improved

For the design analysis:

The entity analysis may be increased by standardization and optimization, although the sheer normalization is not necessary,

The integrity of the database can be improved features such as stored procedure and trigger.

The following are the assumptions that are made during the database design and development state:

Customer records are kept for the job with details such as customer name, address and telephone number.

Customers can order the job more than once.

Job Types are (Single Room, part-home, whole - house) and specific type of job can be incorporated into hard work.

Job Costs depend on the Labor and some use more than one worker and used more than one part included in a job.

A type of labor can be included in a lot of work as job work of various types of work

Specific type of part can make by many suppliers and the supplier can deliver to many parts.

I also want to change these little things to improve my work;

Installation of other complementary system to the exciting system to a more complete system if possible to get

Making the program design more beautiful and complete

Repair the system more effective and efficient for users.

For the safety of my system, which username and password to access the database.

Writing Services

Essay Writing
Service

Find out how the very best essay writing service can help you accomplish more and achieve higher marks today.

Assignment Writing Service

From complicated assignments to tricky tasks, our experts can tackle virtually any question thrown at them.

Dissertation Writing Service

A dissertation (also known as a thesis or research project) is probably the most important piece of work for any student! From full dissertations to individual chapters, we’re on hand to support you.

Coursework Writing Service

Our expert qualified writers can help you get your coursework right first time, every time.

Dissertation Proposal Service

The first step to completing a dissertation is to create a proposal that talks about what you wish to do. Our experts can design suitable methodologies - perfect to help you get started with a dissertation.

Report Writing
Service

Reports for any audience. Perfectly structured, professionally written, and tailored to suit your exact requirements.

Essay Skeleton Answer Service

If you’re just looking for some help to get started on an essay, our outline service provides you with a perfect essay plan.

Marking & Proofreading Service

Not sure if your work is hitting the mark? Struggling to get feedback from your lecturer? Our premium marking service was created just for you - get the feedback you deserve now.

Exam Revision
Service

Exams can be one of the most stressful experiences you’ll ever have! Revision is key, and we’re here to help. With custom created revision notes and exam answers, you’ll never feel underprepared again.