Database System For Marlowe Interiors Cultural Studies Essay

Published:

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

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

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

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

Entity Relationship Diagram

Data Dictionary

Data Dictionary is the heart of a DBMS. Data Dictionary consists of all the definition of the data entries in the system and internal structures. The Data Dictionary of a relational DBMS include:

A description of base relations, including relation names, column names, column data types and null characteristics of columns

Primary key and foreign key declarations, including propagation constrains

A description for views

Declarations of user groups and authorizations

Information about indexes, sizes of files, file structures and clusters

Post relational systems will also store information about procedures , triggers and constraints.

Entity

An entity is the real world objects. This can be tangible, such as a person or intangible, such as account,

Entity : Customer

Description: To store all Customer Data

Identification Attribute: Cus_ID

Other Attributes: Cus_Name,Cus_Adrs,Cus_Ph

Entity : Supplier

Description: To store all Supplier Data

Identification Attribute: Sup_id

Other Attributes: Sup_id,Sup_Adrs,Sup_ph

Entity : Parts

Description: To store all Parts Data

Identification Attribute: Part_id

Other Attributes: Part_Name, Part_Type, Part_Cost

Entity : Job

Description: To store all Job Data

Identification Attribute: Job_id

Other Attributes: Job_Name, Cus_id, Job_Date, Part_id, Job_hour, ImpCost

Entity : Labour

Description: To store all Labour Data

Identification Attribute: Lab_id

Other Attributes: Lab_Name, Lab_Rank, Lab_Adrs

Entity : Labour Type

Description: To store all Labour Data that will involve in a job

Identification Attribute: Part_id

Other Attributes: Part_Name, Part_Type, Part_Cost

Entity : Work

Description: To store all work data

Identification Attribute: Lab_id

Other Attributes: Lab_Type_id

Entity : Supply

Description: To store the supplement of parts

Identification Attribute: Sup_id

Other Attributes: Part_id

Entity : Job Type

Description: To store the types of job

Identification Attribute: Job_id

Other Attributes: Lab_Type_id

Relationships

Relationship ID : R1

Relationship Name : Ordered, Order By

Description : Associate Customer with Job that currently ordered

Participating Entity : Customer

Cardinality : One

Optionality : Optional

Participating Entity : Job

Cardinality : Many

Optionality : Mandatory

Relationship ID : R2

Relationship Name : Used, Used By

Description : Associate Parts with Job that currently used in jobs

Participating Entity : Parts

Lady using a tablet
Lady using a tablet

Comprehensive

Writing Services

Lady Using Tablet

Plagiarism-free
Always on Time

Marked to Standard

Order Now

Cardinality : One

Optionality : Mandatory

Participating Entity : Job

Cardinality : Many

Optionality : Mandatory

Relationship ID : R3

Relationship Name : include, worked by

Description : Associate Job Type with Job that will be performed

Participating Entity : Job

Cardinality : One

Optionality : Mandatory

Participating Entity : Job Type

Cardinality : Many

Optionality : Mandatory

Relationship ID : R4

Relationship Name : include, worked by

Description : Associate Labour Type with Job Type that will be performed

Participating Entity : Job Type

Cardinality : One

Optionality : Mandatory

Participating Entity : Labour Type

Cardinality : Many

Optionality : Mandatory

Relationship ID : R5

Relationship Name : work, worked by

Description : Associate Job Type with work that will be work

Participating Entity : Labour Type

Cardinality : One

Optionality : Mandatory

Participating Entity : Work

Cardinality : Many

Optionality : Mandatory

Relationship ID : R6

Relationship Name : include, work

Description : Associate Labour with Work that will be include in work

Participating Entity : Labour

Cardinality : One

Optionality : Mandatory

Participating Entity : Work

Cardinality : Many

Optionality : Mandatory

Relationship ID : R7

Relationship Name : buy, supply by

Description : Associate Parts with Supply that will be use in work

Participating Entity : Parts

Cardinality : One

Optionality : Mandatory

Participating Entity : Supply

Cardinality : Many

Optionality : Mandatory

Relationship ID : R8

Relationship Name : Supply, Supply by

Description : Associate Job Type with Job that will be performed

Participating Entity : Supplier

Cardinality : One

Optionality : Mandatory

Participating Entity : Supply

Cardinality : Many

Optionality : Mandatory

Attributes

Attribute : Cus_ID

Description : Identifying about Customer's information details

Type : Integer {001, 002, 003, 004, 005, 006,

007, 008, 009, 010}

Attribute : Sup_ID

Description : Identifying about Supplier's information details

Type : Integer {001, 002, 003, 004, 005, 006,

007, 008, 009, 010}

Attribute : Part_ID

Description : Identifying about Parts information details

Type : Integer {001, 002, 003, 004, 005, 006,

007, 008, 009, 010}

Attribute : Job_ID

Description : Identifying about job information details

Type : Integer {001, 002, 003, 004, 005, 006,

007, 008, 009, 010}

Attribute : Job_Type_ID

Description : Identifying about Job Type details

Type : Integer {001, 002, 003, 004, 005, 006,

007, 008, 009, 010}

Attribute : Lab_ID

Description : Identifying about Labour's information details

Type : Integer {001, 002, 003, 004, 005, 006,

007, 008, 009, 010}

Attribute : Lab_Type_ID

Description : Identifying about Labour Type details

Type : Integer {001, 002, 003, 004, 005, 006,

007, 008, 009, 010}

Task 2

Produce the resulting tables clearly indicating primary key andforeign key

Customer File

The customer file include Cus_ID ( Customer ID), Cus_Name( Customer Name), Cus_Adrs(Customer address) and Cus_Ph ( Customer Ph)

No

Name

Type

Description

1.

Cus_ID

Int(11)

Not allow null and unique, ID for Customer

2.

Cus_Name

Varchar(45)

Name of the customer

3.

Cus_Adrs

Varchar(45)

Actual address of the customer

4.

Cus_Ph

Varchar(45)

Phone number of the customer

Primary Key: Cus_ID Foreign Key: No

Supplier File

The supplier file include Sup_ID ( Supplier ID), Sup_Name (Supplier Name), Sup_Adrs (Supplier Address) and Sup_Ph (Supplier Ph)

No

Name

Type

Description

1.

Sup_ID

Int(11)

Not allow null and unique, ID for Supplier

2.

Sup_Name

Varchar(45)

Name of the supplier

3.

Sup_Adrs

Varchar(45)

Actual address of the supplier

4.

Sup_Ph

Varchar(45)

Phone number of the supplier

Primary Key: Sup_ID Foreign Key: No

Parts Table

No

Name

Type

Description

1.

Part_ID

Int(11)

Not allow null and unique ,ID for parts

2.

Part_Name

Varchar(45)

Name of parts

3.

Part_Type

Varchar(45)

Type of parts

4.

Part_Cost

Varchar(45)

Cost of parts

Primary Key: Part_id Foreign Key: No

Labour Table

No

Name

Type

Description

1.

Labour_ID

Int(11)

Not allow null and unique , ID for Labour

2.

Labour_Name

Varchar(45)

Name of Labour

3.

Labour_Rank

Varchar(45)

Rank of Labour, Labour Type

4.

Labour_Adrs

Varchar(45)

The address of Labour

Primary Key: Lab_ID Foreign Key: No

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

Job Table

No

Name

Type

Description

1.

Job_ID

Int(11)

Not allow null and unique , ID for Job

2.

Cus_ID

Int(11)

ID for customer, not null

3.

Part_ID

Int(11)

ID for parts, not null

4.

Job_Date

Date

The start date for a job

5.

Job_Hour

Int(11)

The total hour work for a job

6.

Imp_Cost

Int(11)

The implementation cost for a job

Primary Key: Job_Id Foreign Key: Cus_ID

Part_ID

Labour_Type Table

No

Name

Type

Description

1.

Labour_Type_ID

Int(11)

ID for Labour_Type, Not null and unique

2.

Lab_Type

Varchar(45)

Type of labour

3.

Lab_Cost

Varchar(45)

Cost for labour per hour

Primary Key: Lab_Type_ID Foreign Key: No

Work Table

No

Name

Type

Description

1.

Labour_Type_ID

Int(11)

ID for Labour_Type, Not null

2.

Lab_ID

Int(11)

ID of Labour

Primary Key: Lab_ID

Lab_Type_ID

Job_Type Table

No

Name

Type

Description

1.

Job_ID

Int(11)

ID for Job, Not null

2.

Lab_Type_ID

Int(11)

ID of Labour Type, not null

Primary Key: Job_ID

Lab_Type_ID

Supply Table

No

Name

Type

Description

1.

Part_ID

Int(11)

ID for Part, Not null

2.

Sup_ID

Int(11)

ID of Supplier, not null

Primary Key: Part_ID

Sup_ID

Task 3

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.

Creating tables

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `Assignment` ;

USE `Assignment` ;

Creating Customer Table

CREATE TABLE `customer` (

`Cus_id` int(11) NOT NULL,

`Cus_Name` varchar(45) DEFAULT NULL,

`Cus_Adrs` varchar(45) DEFAULT NULL,

`Cus_Ph` varchar(45) DEFAULT NULL,

PRIMARY KEY (`Cus_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Fig (3.1) Customer Table

Fig (3.2) Test data in Customer Table

Creating Supplier Table

CREATE TABLE `supplier` (

`Sup_id` int(11) NOT NULL,

`Sup_Name` varchar(45) DEFAULT NULL,

`Sup_Adrs` varchar(45) DEFAULT NULL,

`Sup_Ph` varchar(45) DEFAULT NULL,

PRIMARY KEY (`Sup_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Fig (3.3) Supplier Table

Fig (3.4) Test data in supplier table

Creating Parts Table

CREATE TABLE `parts` (

`Part_id` int(11) NOT NULL,

`Part_Name` varchar(45) DEFAULT NULL,

`Part_Type` varchar(45) DEFAULT NULL,

`Part_Cost` varchar(45) DEFAULT NULL,

PRIMARY KEY (`Part_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Fig (3.5) Parts Table

Fig (3.6) Test Data in Parts table

Creating Labour Table

CREATE TABLE `labour` (

`Lab_id` int(11) NOT NULL,

`Lab_Name` varchar(45) DEFAULT NULL,

`Lab_Rank` varchar(45) DEFAULT NULL,

`Lab_Adrs` varchar(45) DEFAULT NULL,

PRIMARY KEY (`Lab_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Fig (3.7) Labour Table

Fig (3.8) Test Data in Labour table

Creating Job Table

CREATE TABLE `job` (

`Job_Name` varchar(45) DEFAULT NULL,

`Cus_id` int(11) DEFAULT NULL,

`Job_Date` date DEFAULT NULL,

`Part_id` int(11) DEFAULT NULL,

`Job_id` int(11) NOT NULL,

`Job_Hour` int(11) DEFAULT NULL,

`impCost` int(11) NOT NULL,

PRIMARY KEY (`Job_id`),

KEY `fk_customer` (`Cus_id`),

KEY `fk_Part` (`Part_id`),

CONSTRAINT `fk_customer` FOREIGN KEY (`Cus_id`) REFERENCES `customer` (`Cus_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,

CONSTRAINT `fk_Part` FOREIGN KEY (`Part_id`) REFERENCES `parts` (`Part_id`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Fig (3.9) Job Table

Fig (3.10) Test data in Job table

Creating Job Type Table

CREATE TABLE `job_type` (

`Job_id` int(11) NOT NULL,

`Lab_Type_id` int(11) NOT NULL,

PRIMARY KEY (`Job_id`,`Lab_Type_id`),

KEY `fk_Job Type_Job1` (`Job_id`),

KEY `fk_Job Type_Labour Type1` (`Lab_Type_id`),

CONSTRAINT `fk_Job Type_Job1` FOREIGN KEY (`Job_id`) REFERENCES `job` (`Job_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,

CONSTRAINT `fk_Job Type_Labour Type1` FOREIGN KEY (`Lab_Type_id`) REFERENCES `labour_type` (`Lab_Type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Fig (3.11) Job_Type Table

Fig (3.12) Test data in Job Table

Creating Labour Type Table

CREATE TABLE `labour_type` (

`Lab_Type_id` int(11) NOT NULL,

`Lab_Type` varchar(45) DEFAULT NULL,

`Lab_Cost` varchar(45) DEFAULT NULL,

PRIMARY KEY (`Lab_Type_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Fig (3.13) Labour Type Table

Fig (3.14) Test data in Labour_type table

Creating Work table

CREATE TABLE `work` (

`Lab_id` int(11) NOT NULL,

`Lab_Type_id` int(11) NOT NULL,

PRIMARY KEY (`Lab_id`,`Lab_Type_id`),

KEY `fk_Work_Labour` (`Lab_id`),

KEY `fk_Work_Labour Type1` (`Lab_Type_id`),

CONSTRAINT `fk_Work_Labour` FOREIGN KEY (`Lab_id`) REFERENCES `labour` (`Lab_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,

CONSTRAINT `fk_Work_Labour Type1` FOREIGN KEY (`Lab_Type_id`) REFERENCES `labour_type` (`Lab_Type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Fig (3.15) Work Table

Fig (3.16) Test data in Work table

Creating Supply Table

CREATE TABLE `supply` (

`Sup_id` int(11) NOT NULL,

`Part_id` int(11) NOT NULL,

PRIMARY KEY (`Sup_id`,`Part_id`),

KEY `fk_Supply_Supplier1` (`Sup_id`),

KEY `fk_Supply_Parts1` (`Part_id`),

CONSTRAINT `fk_Supply_Parts1` FOREIGN KEY (`Part_id`) REFERENCES `parts` (`Part_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,

CONSTRAINT `fk_Supply_Supplier1` FOREIGN KEY (`Sup_id`) REFERENCES `supplier` (`Sup_id`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Fig (1.17) Supply Table

Fig (3.18) Test data in supply table

Task 4

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 `customer_file` AS select `customer`.`Cus_Name` AS `Cus_Name`,`customer`.`Cus_Adrs` AS `Cus_Adrs`,`job`.`Job_id` AS `Job_id`,`job`.`Job_Name` AS `Job_Name`,`job`.`Job_Date` AS `Job_Date` from (`labour` join ((((`parts` join (`labour_type` join (`job_type` join (`customer` join `job` on((`customer`.`Cus_id` = `job`.`Cus_id`))) on((`job_type`.`Job_id` = `job`.`Job_id`))) on((`labour_type`.`Lab_Type_id` = `job_type`.`Lab_Type_id`))) on((`parts`.`Part_id` = `job`.`Part_id`))) join `supply` on((`parts`.`Part_id` = `supply`.`Part_id`))) join `supplier` on((`supply`.`Sup_id` = `supplier`.`Sup_id`))) join `work` on((`labour_type`.`Lab_Type_id` = `work`.`Lab_Type_id`))) on((`labour`.`Lab_id` = `work`.`Lab_id`)));

Fig: Test data in customer_file

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `total` AS select `customer`.`Cus_Name` AS `Cus_Name`,`job`.`Job_Name` AS `Job_Name`,`labour`.`Lab_Name` AS `Lab_Name`,`labour_type`.`Lab_Type` AS `Lab_Type`,((`job`.`Job_Hour` * `labour_type`.`Lab_Cost`) + (`parts`.`Part_Cost` + `job`.`impCost`)) AS `Total` from (`labour` join ((((`parts` join (`labour_type` join (`job_type` join (`customer` join `job` on((`customer`.`Cus_id` = `job`.`Cus_id`))) on((`job_type`.`Job_id` = `job`.`Job_id`))) on((`labour_type`.`Lab_Type_id` = `job_type`.`Lab_Type_id`))) on((`parts`.`Part_id` = `job`.`Part_id`))) join `supply` on((`parts`.`Part_id` = `supply`.`Part_id`))) join `supplier` on((`supply`.`Sup_id` = `supplier`.`Sup_id`))) join `work` on((`labour_type`.`Lab_Type_id` = `work`.`Lab_Type_id`))) on((`labour`.`Lab_id` = `work`.`Lab_id`)));

Fig: Test data in total table

Task 5

Explain any assumptions you have made when analysing, 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

The assumption of a developer appears when the detailed system requirements to the point of he system or the user request (requirements). In task 1 we need to draw a ERD and data dictionary scenario. In task 2 we need to create tables which include the data that will involve in developing the system. Its detailed structure includes the field names, data types, description of the data, primary and foreign keys of the tables. In task 3, using a suitable DBMS to create and insert test data to the tables. By using the SQL (Standard Query Language) and insert the suitable data to the tables and show the test data in the tables. In task 4, requesting to create customer information which is saved to inform for a job requested and data that is require when building an interior design. In task 5, requesting assumptions, analysis, design and implementation that is used in our assignment. The assumption is the key for a developer which shows how he/she think for a system from what point of view. These are assumption that I think in this system:

A customer can employ many jobs so job entity have many ended in Customer and Job relation

A job can include many parts so job have many ended in Job and Parts relation

A labour can be one or more labour type mean he/she can work more than one job so labour and labour_type have many to many relationship which is joined by work entity

A supplier can supply more than one parts and parts can be buy from more than one customer so supplier entity and parts entity have many to many relationship which is joined by supply entity

Fig: ERD of the system

This E-R diagram include following symbols:

The circle means may be zero

Short bar means one

Corw's foot means many

Analyzing System

System analysis consists of two major activities. They are

Requirement elicitation and

Requirements specification

Elicitation is about the system requirements. One requirement is any desire information system or database system in particular. The requirement include following facts:

Requirements may be different stakeholder groups. The requirements are not objective they are only relative to stakeholder's point of view

Requirements may have conflicts.

Requirements must be frozen at some point to build a information technology system component.

Specification is a process in developing, processing modeling. Requirement specification is a model , a concept or logical model. Any model can be seen as a symbol system and a period marked by a collection of some social groups. The symbol in a process of modeling includes these stages:

Communication

Representation

Abstract

Modeling is an important factor for information and communication technology (ICT). There is a section which follows a high point of the database system modeling. Any most accepted method in any form required these elements:

Structure : Structure of elements by mean of modeling

Symbols : The form representing the modeling methods that includes text, images and graphics of mathematical systems. The symbols are the most frequently employment of the information system because of their ease of use

Construction of principle : these refers to the formal and informal rules when constructing models for correct

System Design

The system design includes two stages:

Logical database design

Physical database design

The logical database design

The core theory of the logical design is normalization and functional dependency. Normalization of the relation database system is a process that chooses functions, dependencies between attributes and determines which attribute belong to which table. The result of the logical database system is consistent and minimal redundancy. A normalized database design may not provide best efficiency. It is only a design which is to build business data model. It can involves the reconciliation of the conceptual model to relational mode. This is to achieve independence and standardization of the third paradigm of the model.

The physical database design

This is an important factor of the physical database design is to control replication of data to provide more efficient data access. This can take many steps between the physical designs of relational databases. Which include increase in redundancy, increase in derived columns, folding tables and sub tables. The amount of redundancy will lead to potential inconsistencies or update anomalies. The benefits of replication data are that the DBA can improve the query speed. Drawback is that insertion, deletion and maintenance of data more difficult and necessary additional species of stored data. These are parts of the physical design needs to perform as traffic analysis, transaction analysis, integrity analysis, control analysis and distribution analysis

Implementation of the database

Database implementation includes following stages:

Creating schema

Constructing database storage structure and access mechanisms

Adding indexes, de-normalization

While implementing the system, MySQL control center is used to develop the database. MySQL is database engine designed to use as commercial with GNU license and have separate user interface. I used it because of its ease of use. MySQL Workbench is used to draw an E-R diagram .

The implemented database system is to achieve following outputs

Data structure decleared in appropriate data definition language

Clustering data in approate

Distribution strategy of a database system, including a planned distribution of data

Run query to the database with a set of optimization

Alternative approaches

Database development occurs within the context of information systems develop information systems development is a key to organizations. The key input to the process are ICT resources and developer resources. Labour,Labour_Type and Job can be separated into separate tables. By having them further information about labour and job can be easily inserted. Labour and Labour_Type can be recorded into same table if they are supposed to be the same for all the type and rank holders. Labour_Type can directly associates with Job