Design The Database System For Marlowe Interiors Commerce Essay

September 27, 2017 Commerce

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

Marlowe Interiors keeps a record of the occupations it performs. Jobs are for peculiar clients and classified by occupation type ( Single Room, Part-house, Whole-house ) . The full cost of a occupation depends on the labor used and the parts used. Records of parts and labor should be kept individually.

Labor costs depend on the type of work done. A occupation may hold different kinds of work and use one or more types of worker ( plumber, labourer, qualified builder, interior interior decorator, electrician ) . A worker will work on more than one occupation. The hourly rate of wage for each of these types of worker will change as shown below:

We Will Write a Custom Essay Specifically
For You For Only $13.90/page!


order now

Type

Rate per Hour

Plumber

& A ; lb ; 50

Laborer

& A ; lb ; 20

Qualified Builder

& A ; lb ; 55

Interior Designer

& A ; lb ; 39

Electrician

& A ; lb ; 50

A occupation may affect one or more parts. Examples of parts are: sink, bath, pipe, thermoregulator, wallpaper, adorning, door, light-fitting. The costs of these parts will change and suited trial values should be supplied as portion of the initial execution.

Partss are supplied by assorted providers. The cost of a portion could change depending on the provider.

A client record should be kept for the occupation with all the usual inside informations such as name, reference and phone-number.

Undertaking 1

Undertaking 1 – 30 Marks

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

Task-1.1 ( Entity Relationship Diagram )

Degree centigrades: UsersMaTheinWinDesktopKyaw 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: client

Description: Any individual who confer the occupation on Interior Design Company

Identifying ( properties ) : Criminal Investigation Command

Other properties: cPhNo, cName

Entity: jobtype

Description: Any sort of occupation that is accepted by the client of Interior Design Company

Identifying ( properties ) : jTypeID

Other properties: jType, jHour, eyelid

Entity: laborer

Description: Any individual on the occupation work in Interior Design Company

Identifying ( properties ) : eyelid

Other properties: jTypeID, jTypeID, lName

Entity: laborer type

Description: Any sort of individual on the occupation work in Interior Design Company

Identifying ( properties ) : lTypeID

Other properties: lType, RatepHour

Entity: occupation

Description: Any occupation that is accepted by the client of Interior Design Company

Identifying ( properties ) : jID

Other properties: pelvic inflammatory disease, Criminal Investigation Command, jTypeID

Entity: portion

Description: Any component that is used for making the Job of Interior Design Company

Identifying ( properties ) : pelvic inflammatory disease

Other properties: pType, pPrice, pImCost, partcol

Entity: supply

Description: Give something utile or necessary to the occupation of Interior Design

Company

Identifying ( properties ) : sID, pelvic inflammatory disease

Other properties: –

Entity: provider

Description: Any individual who supplies parts for making the occupation in Interior Design Company

Identifying ( properties ) : sID

Other properties: sName, sAddress, sPhNo

1.2.2 Relationship

Relationship ID: R1

Relationship Name: Give, Received

Description: Relation between with client and occupation which it is performed with all usual inside informations

Participating Entity: client

Cardinality: 1

Optionality: mandatary

Participating Entity: occupation

Cardinality: many

Optionality: mandatary

Relationship ID: R2

Relationship Name: Received, isReceived

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

Participating Entity: occupation

Cardinality: many

Optionality: mandatary

Participating Entity: jobtype

Cardinality: 1

Optionality: mandatary

Relationship ID: R3

Relationship Name: isReceived, Employ

Description: Relation between with jobtype and laborer who will work on the occupation

Participating Entity: jobtype

Cardinality: 1

Optionality: mandatary

Participating Entity: laborer

Cardinality: many

Optionality: mandatary

Relationship ID: R4

Relationship Name: Employ, Employed

Description: Relation between with laborer and laborer type who will work on the occupation

Participating Entity: laborer

Cardinality: many

Optionality: mandatary

Participating Entity: labourertype

Cardinality: 1

Optionality: mandatary

Relationship ID: R5

Relationship Name: Received, is used

Description: Relation between with occupation and portion which is used for the occupation

Participating Entity: occupation

Cardinality: many

Optionality: mandatary

Participating Entity: portion

Cardinality: 1

Optionality: mandatary

Relationship ID: R6

Relationship Name: is used, issupplied

Description: Relation between with portion which is supplied from the provider and supply is used

Participating Entity: portion

Cardinality: 1

Optionality: mandatary

Participating Entity: supply

Cardinality: many

Optionality: mandatary

Relationship ID: R7

Relationship Name: issupplied, supply

Description: Relation between with supply and provider who supply the parts for the occupation

Participating Entity: supply

Cardinality: many

Optionality: mandatary

Participating Entity: provider

Cardinality: 1

Optionality: optional

1.2.3 Property

Property: Criminal Investigation Command

Description: Customer ‘s designation Number

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

Property: jID

Description: Job ‘s designation Number

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

Property: jtypeID

Description: Identification Number of Job type

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

Property: eyelid

Description: Labour ‘s designation Number

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

Property: lTypeID

Description: Identification Number of labour type

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

Property: pelvic inflammatory disease

Description: Part ‘s designation Number

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

Property: sID

Description: Supplier ‘s designation Number

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

1.2.4 ( Integrity Constraints )

A Job may hold different type of work and utilize One or more types of workers

A worker will work on more than one occupation.

A Job may affect one or more parts.

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

Partss and labour records should be kept individually and overall cost will depend on the Partss used and the Labour used.

Records of client should be kept for occupation with the inside informations such as name, reference and phone-number.

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

Partss are supplied by different provider and the cost of the portion vary depend on the different provider.

Undertaking 2

Undertaking 2 – 20 Marks

Produce the resulting tabular arraies clearly bespeaking the primary and foreign keys.

client

The client tabular array is to hive away the information of the client including client ID, client name, and the phone figure.

No

Name

Description

Type

Key

1

Criminal Investigation Command

Customer ‘s Identification Number

Integer

PK

2

cName

Name of the client

VARCHAR ( 30 )

_

4

cPhNo

Phone Number of the Customer

VARCHAR ( 20 )

_

occupation

This tabular array is to hive away the the Job Information including the pelvic inflammatory disease, Criminal Investigation Command, jID and jTypeID.

No

Name

Description

Type

Key

1

pelvic inflammatory disease

Part ‘s Identification Number

Integer

FK

2

Criminal Investigation Command

Customer ‘s Identification Number

Integer

FK

3

jID

Job ‘s Identification Number

Integer

PK

4

jTypeID

Identification Number of occupation type

Integer

FK

jobType

This tabular array is to hive away the occupation type information including the jHour, jType, jCost, eyelid and jTypeID.

No

Name

Description

Type

Key

1

jHour

Hour of occupation

Integer

2

eyelid

Labourer ‘s Identification Number

Integer

3

jType

Type of Job

VARCHAR ( 30 )

4

jTypeID

Identification Number of occupation type

Integer

PK

laborer

This tabular array is to hive away the laborer information including the eyelid, jTypeID, lTypeID and lName.

No

Name

Description

Type

Key

1

eyelid

Labourer ‘s Identification Number

Integer

PK

2

jTypeID

Identification Number of occupation type

Integer

FK

3

lTypeID

Identification Number of laborer type

Integer

FK

4

lName

Name of laborer

VARCHAR ( 45 )

labourerType

This tabular array is to hive away the type of the laborer ‘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 hr

Integer

3

lTypeID

Identification Number of laborer type

Integer

PK

portion

This tabular array is to hive away the portion information including the pelvic inflammatory disease, pType, pPrice, pImCost and partcol.

No

Name

Description

Type

Key

1

pelvic inflammatory disease

Part ‘s Identification Number

Integer

PK

2

pType

Type of portion

VARCHAR ( 30 )

3

pPrice

Monetary value of portion

Integer

4

pImCost

Execution cost of portion

Integer

5

partcol

Coloring material of portion

VARCHAR ( 45 )

supply

This tabular array is to hive away the supply information including the pelvic inflammatory disease and sID.

No

Name

Description

Type

Key

1

pelvic inflammatory disease

Part ‘s Identification Number

Integer

PK

2

sID

Supply ‘s Identification Number

Integer

PK

provider

This tabular array is to hive away the provider ‘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 provider

VARCHAR ( 30 )

3

sAddress

Address of provider

VARCHAR ( 30 )

4

sPhNo

Phone figure of provider

VARCHAR ( 20 )

Undertaking 3

Undertaking 3 – 20 Marks

Using a Database Management System ( DBMS ) of your pick, set-up all of the above normalised tabular arraies, and dwell them with well-designed trial informations ( minimal 5 records per tabular array ) . Provide printouts of all tabular arraies.

Reasonable premises may be made with respect to informations.

Customer tabular array

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 tabular array with proving informations

Job tabular array

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 tabular array with proving informations

Jobtype tabular array

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 tabular array with proving informations

Labourer tabular array

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 tabular array with proving informations

Labourertype tabular array

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 tabular array with proving informations

Part tabular array

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 tabular array with proving informations

Supplier tabular array

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 tabular array with proving informations

Supply tabular array

CREATE TABLE `supply` (

`sID` int ( 11 ) NOT NULL,

`pID` int ( 11 ) NOT NULL,

PRIMARY KEY ( `sID` , `pID` )

)

Figure ( 3.8 ) ; Supply tabular array with proving informations

Undertaking 4

Undertaking 4 – 20 Marks

Set-up and test all of the undermentioned questions utilizing Structured Query Language ( SQL ) . Provide printouts of SQL codification for each question and the end product produced when you run the question in the database you have developed:

Display the names and references of the clients of Marlowe Interiors and the occupations and occupation types that have been carried out for them.

Expose a full record of a occupation. This should include the name of the client, the occupation type, the workers on the occupations and what type they are, the parts included in the occupation and the concluding overall cost for the occupation.

( A ) SQL for exposing the names and references of the client and the occupations and occupation types that have been carried out for them.

# Host: localhost

# Database: Marlowe insides

# Table: ‘jobforcustomer ‘

#

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

Figure ( 4.1 ) : Consequence tabular array for TASK ( 4-A )

( B ) SQL for exposing a full record of a occupation that include the name of the client, the occupation type, the workers on the occupations and what type they are, the parts included in the occupation and the concluding overall cost for the occupation

# Host: localhost

# Database: Marlowe insides

# Table: ‘totaljobcost ‘

#

CREATE ALGORITHM=UNDEFINED DEFINER=`root` @ `localhost` SQL SECURITY DEFINER VIEW `totaljobcost` AS choice `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` articulation ( ( `part` articulation ( `labourertype` articulation ( ( `jobtype` articulation ( `customer` articulation `job` on ( ( `customer`.`cID` = `job`.`cID` ) ) ) on ( ( `jobtype`.`jTypeID` = `job`.`jTypeID` ) ) ) articulation `labourer` on ( ( `jobtype`.`jTypeID` = `labourer`.`jTypeID` ) ) ) on ( ( `labourertype`.`lTypeID` = `labourer`.`lTypeID` ) ) ) on ( ( `part`.`pID` = `job`.`pID` ) ) ) articulation `supply` on ( ( `part`.`pID` = `supply`.`pID` ) ) ) on ( ( `supplier`.`sID` = `supply`.`sID` ) ) ) ;

Figure ( 4.2 ) : Consequence tabular array for TASK ( 4-B )

Undertaking 5

Analyze on Database Management System being used

The chief end products from physical database design are:

-Volume analysis

-Usage/transaction analysis

-Integrity analysis

-Control/security analysis

-Distribution analysis

Database execution involves the undermentioned activities:

Making the physical scheme,

Establishing storage constructions and associated entree mechanisms

Adding indexes, conductivity de-normalization

Where appropriate

Exploiting the installations of the chosen DBMS and implementing unity restraints

Making the physical scheme involves implementation the information constructions in the chosen database linguistic communication.

Storage constructions for the database objects ate established utilizing the options of the chosen DBMS to run into the demands of use.

Indexs are used to better retrieval public presentation and will be guided by the analysis of entree demands.

De-normalization of the physical scheme may be undertaken to better public presentation.

De-normalization should be used with attention.

Knowing about the installations of the chosen DBMS and how to utilize these to polish the database is of import

Appropriate ways are chosen from the following to implement built-in and extra unity restraints: inherently, procedurally of non-procedurally.

Database Management System is used here is MySQL Control Center 0.9.4. If MySQL is an unfastened beginning linguistic communication, the cost of utilizing the system is free. Besides, the RDBMS system and can be extended to enterprise degree database system.

When I got the database design developed for the Interior Design Company, I have to make these four stages. They are requirement analysis, system analysis and system design and execution phases. The demand analysis phase I gather the necessary informations for the necessity of the demand evocation. In the system analysis stage, I use the conceptual theoretical account. In the system design, I use logical theoretical account. In the execution, use the physical theoretical account.

One of the chief advantages of utilizing MS SQl is the stored processs. Stored process is the lines of codification which the application calls and it is store on the waiter to roll up into a pre-fast response clip.

Tranction log is a record in the object to recover, update and delete records. Two grounds of utilizing dealing logs is ther push back of the process and the dealing is recorded for the security intent. Rollback process is used in the accedental updates and delections which the database decision maker utilizing the dealing log back to the original information record can be returned. If a breach of security was suspects by the decision maker, he can seek the dealing informations of all types of logs which can be viewed to find the badness of misdemeanors.

Changes to be improved

For the design analysis:

The entity analysis may be increased by standardisation and optimisation, although the sheer standardization is non necessary,

The unity of the database can be improved characteristics such as stored process and trigger.

The followers are the premises that are made during the database design and development province:

Customer records are kept for the occupation with inside informations such as client name, reference and telephone figure.

Customers can order the occupation more than one time.

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

Occupation Costss depend on the Labor and some use more than one worker and used more than one portion included in a occupation.

A type of labour can be included in a batch of work as occupation work of assorted types of work

Specific type of portion can do by many providers and the provider can present to many parts.

I besides want to alter these small things to better my work ;

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

Making the plan design more beautiful and complete

Repair the system more effectual and efficient for users.

For the safety of my system, which username and watchword to entree the database.

x

Hi!
I'm Amanda

Would you like to get a custom essay? How about receiving a customized one?

Check it out