Marlowe Interiors Entity Relationship Data Model. Custom and Job Specific Diagram. This diagram is showing each Customer and Job Type can have multiple jobs. In this diagram you may see one to many relationships linking to the Job entity. For example one customer can have single, part-house or whole-house job type. Base on the job type it will involve several kinds of job for customer requirement.
Job Type Specific Diagram
This diagram is showing the Job Type which may contain unique types like Part-House, Whole-House or Single Room either one by every request. You may see one to one relationship linking each other.
Jobs Specific Diagram
This diagram is showing the Job which can involve multiple Parts & Labour. You may see one to many relationships linking to Job Entity. For example one job can have several kinds of labour, base on the job & the job of labour it will involve several kinds of parts for job requirement.
Labour Job Specific Diagram
This diagram is showing the Labour Job entity, each Labour can work for multiple Job depends on the Job Type. You may see the one to many relationships linking to Labour Job entity. For example Labour can have multiple jobs like single, part-house or whole-house job type. Base on the job type it will involve several kinds of labour Job requirement like electrician, interior designer, qualified builder, plumber or labourer etc....
Parts Specific Diagram
This diagram is showing the Parts which contain multiple components depend on the Job requirements. You may see one to many relationships linking to Parts entity. For example if the job type is single room, it may contains several kinds of parts type such as wallpaper, sink, decking, door, light-fitting, sink, bath, pipe or thermostat etc…
Parts Type & Supplier Specific Diagram
This diagram is showing the Parts which can be involved multiple supplier depend on the cost & quality. In fact higher quality the costing could be more expensive. Reversely lower quality the costing could be cheaper. You may see many to many relationships linking each other. For example the parts wallpaper can have thousand types of pattern, paper quality, cutting or size etc… plus several suppliers in the world wide.
Overall Summary
Task 2
All Tables with Primary and Foreign Key Diagram
Customer Table
Customer
PK
Customer ID
Customer Name
Customer Address
Customer Phone
This diagram is showing the customer table which contains Customer information like Name, Address, Telephone number & ID. The Customer_ID is the primary key for this table.
Here is the Database sample
Job Type Table
Job Type
PK
Job Type ID
Job Type Name
This diagram is showing the job type table which contains Job Type information like Name & ID. The Job_Type_ID is the primary key for this table.
Here is the Database sample
Labour Job Table
Labour Job
PK
Labour Job ID
Labour Job Name
Labour Job Cost
This diagram is showing the labour job table which contains the job of Labour information like Name, the cost of labour & ID. The Labour_Job_ID is the primary key for this table.
Here is the Database sample
Supplier Table
Supplier
PK
Supplier ID
Supplier Name
Supplier Cost
This diagram is showing the supplier table which contains Supplier information like Name, the cost of supplier & ID. The Supplier_ID is the primary key for this table.
Here is the Database sample
Job Table
Job
PK
Job ID
Job No
FK
Job Type ID
FK
Customer ID
FK
Parts ID
FK
Labour Job ID
Labour Job Hour
This diagram is showing the Job table which contains job information like Job number, the key of Job Type, the key of Customer ID, the key of Parts ID, the key of Labour ID, the hour if Labour Job & ID. The Job_ID is the primary key for this table.
Here is the Database sample
Parts Table
Parts
PK
Parts ID
Parts Name
FK
Supplier ID
This diagram is showing the parts table which contains Part information like Name, the key of supplier ID & ID. The Parts_ID is the primary key for this table.
Here is the Database sample
Parts & Supplier Table
Parts
PK
Parts ID
Parts Name
FK
Supplier ID
Supplier
PK
Supplier ID
Supplier Name
Supplier Cost
This diagram is showing the relationship between Parts & Supplier table. The Supplier_ID is the primary key of Supplier table; in the meantime it is the foreign key of Parts table.
Here is the Database sample
Overall Summary
This diagram is showing the relationship between Job, Parts, Customer, Job Type, Labour Job & Supplier table. Base on the Job table, the Job_ID is the primary key for the Job table, in the meantime Job_Type_ID, Customer_ID, Parts_ID & Labour_Job_ID is the foreign keys of the Job table. For the Supplier table the Supplier_ID is the primary key which is the foreign key of the Parts table.
Task 3
According with the Database Management System, I'm using Microsoft SQL server 2000 version to demonstrate the screen shot, print out, testing & query results.
For the Customer table, I will define the Customer_ID, Customer_Name, Customer_Address & Customer_Phone fields to store customer records. The primary key is Customer_ID which is unique. Here is the Customer table definition:-
Customer
PK
Customer ID
Varchar
50
Not Null
Customer Name
Varchar
255
Not Null
Customer Address
Varchar
255
Not Null
Customer Phone
Varchar
50
Not Null
Please see the screenshot diagram as the following:-
Here is the completed result that shown the record set of the Customer table:-
Customer Table
CS0001
ABC Company
Room 1201, 12/F, Mass Mutual Tower, Wan Chai, Hong Kong
852 23945757
CS0002
Ychoo Inc
Room 2001, 22/.F, Central Plaza Tower, Central, Hong Kong
852 24810331
CS0003
AECN
Unit 1-3, 20/F, Wei On Center, Sheung Wan, Hong Kong
852 29348481
CS0004
123 Company
1805-1810, 18/F, Winsor House, Causeway bay, Hong Kong
852 23948737
CS0005
Micaware
1/F, Hung Tung Building, Kwun Tong, Kowloon, Hong Kong
852 29484841
Here is the database sample
Here is the syntax of Structure Query Language to querying the record set of the Customer table:-
SELECT *
FROM tb_Customer
Here is the completed result that using Structure Query Language to querying the record set of the Customer table:-
For the Job Type table, I will define the Job_Type_ID & Job_Type_Name fields to store job type records. The primary key is Job_Type_ID which is unique. Here is the Job Type table definition:-
Job Type
PK
Job Type ID
Varchar
50
Not Null
Job Type Name
Varchar
255
Not Null
Please see the screenshot diagram as the following:-
Here is the completed result that shown the record set of the Job Type table:-
Job Type Table
T0001
Single Room
T0002
Part-House
T0003
Whole-House
Here is the database sample
Here is the syntax of Structure Query Language to querying the record set of the Job Type table:-
SELECT *
FROM tb_Job_Type
Here is the completed result that using Structure Query Language to querying the record set of the Job Type table:-
For the Labour Job table, I will define the Labour_Job_ID, Labour_Job_Name & Labour_Job_Cost fields to store labour job records. The primary key is Labour_Job_ID which is unique. Here is the Labour Job table definition:-
Labour Job
PK
Labour Job ID
Varchar
50
Not Null
Labour Job Name
Varchar
50
Not Null
Labour Job Cost
Money
8
Not Null
Please see the screenshot diagram as the following:-
Here is the completed result that shown the record set of the Labour Job table:-
Labour Job Table
L0001
Plumber
50
L0002
Labourer
20
L0003
Qualified Builder
55
L0004
Interior Designer
39
L0005
Electrician
50
Here is the database sample
Here is the syntax of Structure Query Language to querying the record set of the Labour Job table:-
SELECT *
FROM tb_Labour_Job
Here is the completed result that using Structure Query Language to querying the record set of the Labour Job table:-
For the Supplier table, I will define the Supplier_ID, Supplier_Name & Supplier_Cost fields to store supplier records. The primary key is Supplier_ID which is unique. Here is the Supplier table definition:-
Supplier
PK
Supplier ID
Varchar
50
Not Null
Supplier Name
Varchar
50
Not Null
Supplier Cost
Money
8
Not Null
Please see the screenshot diagram as the following:-
Here is the completed result that shown the record set of the Supplier table:-
Supplier Table
S0001
Wing Fung Company
200
S0002
Fu Tai Manufacturing
500
S0003
Man Shing Electoric
900
S0004
Roca Bathing Supply
1250
S0005
Wooden Paint
650
S0006
Sun Flower Company
999
Here is the database sample
Here is the syntax of Structure Query Language to querying the record set of the Supplier table:-
SELECT *
FROM tb_Supplier
Here is the completed result that using Structure Query Language to querying the record set of the Supplier table:-
For the Parts table, I will define the Parts_ID, Parts_Name & Supplier_ID fields to store parts records. The primary key is Parts_ID which is unique & Supplier_ID is the foreign key. Here is the Parts table definition:-
Parts
PK
Parts ID
Varchar
50
Not Null
Parts Name
Varchar
255
Not Null
FK
Supplier ID
Varchar
50
Not Null
Please see the screenshot diagram as the following:-
Here is the completed result that shown the record set of the Parts table:-
Parts Table
P0001
Sink
S0003
P0002
Light-fitting
S0001
P0003
Bath
S0004
P0004
Pipe
S0002
P0005
Thermostat
S0001
P0006
Wallpaper
S0005
P0007
Decking
S0001
P0008
Door
S0001
Here is the database sample
Here is the syntax of Structure Query Language to querying the record set of the Parts table:-
SELECT *
FROM tb_Parts
Here is the completed result that using Structure Query Language to querying the record set of the Parts table:-
For the Job table, I will define the Job_ID, Job_No, Job_Type_ID, Customer_ID, Parts_ID, Labour_Job_ID & Labour_Job_Hour fields to store job records. The primary key is Job_ID which is unique Job_Type_ID, Customer_ID, Parts_ID & Labour_Job_ID is the foreign key. Here is the Job table definition:-
Job
PK
Job ID
Int
4
Not Null
Job No
Varchar
255
Not Null
FK
Job Type ID
Varchar
50
Not Null
FK
Customer ID
Varchar
50
Not Null
FK
Parts ID
Varchar
50
Not Null
FK
Labour Job ID
Varchar
50
Not Null
Labour Job Hour
Decimal
9
Not Null
Please see the screenshot diagram as the following:-
Here is the completed result that shown the record set of the Job table:-
Job Table
1
J20110110
T0001
CS0001
P0003
L0002
10
2
J20110110
T0001
CS0001
P0002
L0001
5
3
J20110110
T0001
CS0001
P0004
L0003
4
4
J20110123
T0002
CS0003
P0003
L0001
6
5
J20110123
T0002
CS0003
P0002
L0005
2
6
J20110110
T0001
CS0001
P0001
L0001
3
7
J20110115
T0003
CS0005
P0006
L0004
20
8
J20110115
T0003
CS0005
P0001
L0003
7
Here is the database sample
Here is the syntax of Structure Query Language to querying the record set of the Job table:-
SELECT *
FROM tb_Job
Here is the completed result that using Structure Query Language to querying the record set of the Job table:-
Task 4
Here is the Structured Query Language statement for displaying record including the fields of names, addresses of customer, jobs and jobs types. Please see the SQL statement as the following:-
SELECT
Job.Job_No,
Customer.Customer_Name,
Customer.Customer_Address,
JobType.Job_Type_Name
FROM
tb_Job as Job
LEFT OUTER JOIN tb_Customer as Customer
ON (Customer.Customer_ID = Job.Customer_ID)
LEFT OUTER JOIN tb_Job_Type as JobType
ON (JobType.Job_Type_ID = Job.Job_Type_ID)
For better performance I have optimized the "SELECT" statement by specific columns to prevent the large amount of data flushing. In the meantime, I used the Alias to represent table name for simplifying & easier reading & referencing column. During the Job table contains several foreign keys all relevant tables such as Customer & Job Types will join into the Job table at the same time.
Here is the Structure Query Language query results table:-
Job_No
Customer_Name
Customer_Address
Job_Type_Name
J20110110
ABC Company
Room 1201, 12/F, Mass Mutual Tower, Wan Chai, Hong Kong
Single Room
J20110110
ABC Company
Room 1201, 12/F, Mass Mutual Tower, Wan Chai, Hong Kong
Single Room
J20110110
ABC Company
Room 1201, 12/F, Mass Mutual Tower, Wan Chai, Hong Kong
Single Room
J20110110
ABC Company
Room 1201, 12/F, Mass Mutual Tower, Wan Chai, Hong Kong
Single Room
J20110123
AECN
Unit 1-3, 20/F, Wei On Center, Sheung Wan, Hong Kong
Part-House
J20110123
AECN
Unit 1-3, 20/F, Wei On Center, Sheung Wan, Hong Kong
Part-House
J20110110
ABC Company
Room 1201, 12/F, Mass Mutual Tower, Wan Chai, Hong Kong
Single Room
J20110115
Micaware
1/F, Hung Tung Building, Kwun Tong, Kowloon, Hong Kong
Whole-House
J20110115
Micaware
1/F, Hung Tung Building, Kwun Tong, Kowloon, Hong Kong
Whole-House
In the results table there are 9 rows of record exists, each column represent its owned value such as the field of Job No, Customer Name, Customer Address & Job Type.
Here is another full picture of relational database management system diagram, it has shown 3 tables primary key & foreign key linking relationship, full syntax of SQL statement & the result table of record set:-
Here is the Structured Query Language statement for displaying full record of a job including the fields of names, jobs types, workers, parts & the overall cost. Please see the SQL statement as the following:-
SELECT
Job.Job_No,
Customer.Customer_Name,
JobType.Job_Type_Name,
LabourJob.Labour_Job_Name,
(LabourJob.Labour_Job_Cost * Job.Labour_Job_Hour) AS CostofLabour,
Parts.Parts_Name,
Supplier.Supplier_Cost,
((LabourJob.Labour_Job_Cost * Job.Labour_Job_Hour) + Supplier.Supplier_Cost) AS SubTotal
FROM
tb_Job as Job
LEFT OUTER JOIN tb_Customer as Customer
ON (Customer.Customer_ID = Job.Customer_ID)
LEFT OUTER JOIN tb_Job_Type as JobType
ON (JobType.Job_Type_ID = Job.Job_Type_ID)
LEFT OUTER JOIN tb_Labour_Job as LabourJob
ON (LabourJob. Labour_Job_ID = Job. Labour_Job_ID)
LEFT OUTER JOIN tb_Parts as Parts
ON (Parts.Parts_ID = Job.Parts_ID)
LEFT OUTER JOIN tb_Supplier as Supplier
On (Supplier.Supplier_ID = Parts.Supplier_ID)
For better performance I have optimized the "SELECT" statement by specific columns to prevent the large amount of data flushing. In the meantime, I used the Alias to represent table name for simplifying & easier reading & referencing column. During the Job table contains several foreign keys all relevant tables such as Customer, Job Types, Labour Job, Parts & Supplier will join into the Job table at the same time.
Here is the Structure Query Language query results table:-
Job_No
Customer_Name
Job_Type
Labour_Job
CostofLabour
Parts_Name
Supplier_Cost
SubTotal
J20110110
ABC Company
Single Room
Labourer
200
Bath
1250
1450
J20110110
ABC Company
Single Room
Plumber
250
Light-Fitting
200
450
J20110110
ABC Company
Single Room
Qualified Builder
220
Pipe
500
720
J20110123
AECN
Part-House
Plumber
300
Bath
1250
1550
J20110123
AECN
Part-House
Electrician
100
Light-Fitting
200
300
J20110110
ABC Company
Single Room
Plumber
150
Sink
900
1050
J20110115
Micaware
Whole-House
Interior Designer
780
Wallpaper
650
1430
J20110115
Micaware
Whole-House
Qualified Builder
385
Sink
900
1285
In the results table there are 8 rows of record exists, each column represent its owned value such as the field of Job No, Customer Name, Job Type, Labour Job, Parts, Supplier Cost & two expression columns such as Cost of Labour & Sub Total of each record. The Cost of Labour is an expression column calculated by Labour per Hour x Labour of Cost. The Subtotal column also is an expression column calculated by Cost of Labour + Cost of Supplier. This calculation expression method does not keeping a real record or allocation the database storage. It is flexible & dynamic to accounting any combination of column immediately & cached for query result set. Also it does not require changing the actual data of the database record.
Here is another full picture of relational database management system diagram, it has shown 6 tables primary key & foreign key linking relationship, full syntax of SQL statement & the result table of record set:-
In fact, the relational database management system can be driven & apply into several custom layouts such as report, view or application forms etc…Here are some samples of the Marlowe Interiors Job Summary carry out by the system, all selected fields are presented in the form as the following:-
Here is output sample for Job Summary
Marlowe Interiors
Job Summary
Job No: J20110110
Job Type: Single Room
Customer: ABC Company
Address: Room 1201, 12/F, Mass Mutual Tower,
Wan Chai, Hong Kong
Phone: 852 23945757
Labour Costing
Item
Description
Amount
1
Labourer
$200
2
Plumber
$250
3
Qualified Builder
$220
SubTotal
$670
Parts Costing
Item
Description
Amount
1
Bath
$1,250
2
Light-Fitting
$200
3
Pipe
$500
SubTotal
$1,950
Total Amount: $2,620
Here is output sample for Job Summary
Marlowe Interiors
Job Summary
Job No: J20110123
Job Type: Part-House
Customer: AECN
Address: Unit 1-3, 20/F, Wei On Center, Sheung Wan,
Hong Kong
Phone: 852 29348481
Labour Costing
Item
Description
Amount
1
Electrician
$100
2
Plumber
$300
SubTotal
$400
Parts Costing
Item
Description
Amount
1
Bath
$1,250
2
Light-Fitting
$200
SubTotal
$1,450
Total Amount: $1,850
Here is output sample for Job Summary
Marlowe Interiors
Job Summary
Job No: J20110123
Job Type: Whole-House
Customer: Micaware
Address: 1/F, Hung Tung Building, Kwun Tong, Kowloon,
Hong Kong
Phone: 852 29484841
Labour Costing
Item
Description
Amount
1
Interior Designer
$780
2
Qualified Builder
$385
SubTotal
$1,165
Parts Costing
Item
Description
Amount
1
Wallpaper
$650
2
Sink
$900
SubTotal
$1,550
Total Amount: $2,715
Task 5
Conclusion
I assume that we designated a series user interface of forms for inputting, modifying & updating data. Think about it is not easy to work on database management system by normal user without professional technique directly. Make it simple & straight forward to guide user step by step looks friendly is almost important. Defining a simple, normalized & indexed table is also important key objective. It can make data consistency and easy for future upgrade, maintenance or business migration when the issue is raised. We also designated a series of customer reports to help user to print out reports such as forms, labour costing, supplier cost, inventory summary, invoice, quotation, statistic & daily report etc… for outputting data.
In the meantime, we will predefine a series of backup plan and scheduled to backing up all information data automatically. Once it requires to rollback or revision data in case there is a series of backup can be restored.
According with the security issues, we will pre-define several kind of user privileges in order to fulfill their job requirement like some users can inserting a record only, some users can inserting & updating record, some users can read & print only, some users can have full rights inserting, updating & modifying records etc…
Overall & reviewing the whole project, I had been choosing Microsoft SQL server as the relational database management system software, it is an industrial standard format, well organize for development environment, the advantage is using native structure query language formatting the querying syntax which query data become more faster than a single spreadsheet like excel or access single file structure. It is also more flexible & high availability for a large scale environment and can handle many complex calculation or thousand million of record set. When designing a database it requires normalizing tables in order to save de-duplicate records and repeating groups for better database performance once optimized.
Well indexing the table can improve the searching time in shortly. Setting up a primary key & foreign key belongs to the table linking their relationship. This identity value is a unique value which means no duplicate identity record can be able to store in the same table. It can preventing the repeating records & normalized the table for taking in good shape designation. Also the foreign key can be used to cross-referencing two or more tables for matching relational table. Using an alias can simplify the long table name for easier reading & referencing column.
Optimizing the select statement can improve a faster result set by returning specific columns & get rid of unnecessary data. Somehow it also saved the bandwidth if query a large amount of record set or on a slow network environment. It can also support customizing views & reports. For getting a better improvement, it can be implementing or deploy stored procedure, most frequency SQL statement can load into the memory & cached to saved the loading on every time.
On the other hand, applying the order statement can be easier for reading in sequential ordered up/down direction. Using expression method is taking an advantage for accounting complex calculation without changing any records or real data. It does not save or allocate any capacity of the storage.
Using Entity Relationship Diagram data modeling is helping me easy to more understanding the relationships between entities and entities in graphical representation.
Using the relational database management system are highly normalized but less or limited abstraction. In order to take more abstraction it can be integrated Object Oriented data modeling. Most popular designation is mixed to approach fewer entities or classes. It is more flexible for future upgrade during the business development or requirement changed. Also it doesn't intensive for the changing then relational database management system while it is robust.
Data security is also a main concern for organization / corporate. The advantage of using database system can define into several accessing levels. User cannot direct accessing the data without the proper rights or privilege. Data is also more secure when keeping in the place which cannot be taken, copy or move out somewhere like a file system or damaged by human accident or mistakes.
In addition, data recovery is also a critical concern for organization / corporate. A well organized recovery planning on the database management system can help them to reduce the down time. It may support schedule back up or restoring data in specific date time. Setup redundancy solution can obtain & take the benefit of the risk management.
Referencing and Bibliographies
http://www.microsoft.com/sqlserver/
http://office.microsoft.com/zh-hk/visio/
http://office.microsoft.com/
http://en.wikipedia.org/wiki/relational_database_management_system/
http://www.databasesecurity.com/
