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:
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.
