IT Database / ERD Task

P a g e | 5 list of the customer base and be able to contact them for future purchases. This will allow everyone to pull up specific machine data and provide better support than the current file cabinet system.

Section 1 Introduction (cont.) Type of Business System The current proposal for this database will be a used mainly by the equipment manager, so the system would need to be a single user desktop database. Business Process This system will address the need the company faces with record keeping. This system will maintain service records, machine records, salesmen records as well as a customer database. This system will have the ability to generate different reports to assist the sales and service departments towards their yearly goals. System Users The primary user of this system will be the office manager. This user will be the one to generate any reports that need done as well as enter new data in the system. This system will be able to be used by other managers if needed, but would prefer to keep this to a single user. This will prevent any records being damaged or improperly changed. Business Rules 1. Many customers can purchase many machines, but only one salesman per customer. 2. A salesman can make many sells, but only one salesman per sale. 3. Many machines can be sold, but only one sales ID per machine sold. 4. Many machines can have many services. P a g e | 6 Section 2 ERD Design P a g e | 7 Section 3 Logical and Physical Design Database Architecture Introduction It is important to have a properly designed database so that accurate information can be provided to an organization. It is much easier to create an effective design initially so that necessary modifications to the database are kept at a minimum. Discovering problems after a database has been put into operation can be detrimental to a business, institution or organization.

There is a three-phase process in developing a database:

1. Logical design: defining tables, fields, Primary and Foreign keys, establishing table relationships and levels of data integrity.

2. Implementation of the logical design: using a DBMS to create tables and their relationships, using the tools to implement levels of data integrity. 3. Development of end-user application.

Proposed Model I will be using one of the most popularly used database model the relational database model to build database of this particular system.

This model provides a declarative method for specification of data and queries. In a relational model of a database, the database users directly state the information they want to be retrieved from it and abstract away from the responsibility of describing the data structures that are used to store data. A relational database is a collection of relations or tables. The rows of a table in a relational database are known as tuples and each column of a table is called an attribute. By definition, a relation becomes a set of tuples having the same attributes. Operations, which can be performed on the relations are select, project and join. The join operation combines relations, the select queries are used for P a g e | 8 data retrieval and the project operation identifies attributes. Similar to other database models, even relational databases s upport the insert, delete and update operations. Section 3 Logical and Physical Design (cont.) Why I have chosen Relational Model Basically, relational databases are based on relational set theory. Normalization is a vital component of relational model of databases.

Relational operations, supported by the relational databases, work best with normalized tables. A relational database supports relational algebra, consequently supporting the relational operations of the set theory. Apart from mathematical set operations namely, union, intersection, difference and Cartesian product, relational databases also support select, project, and relational join and division operations. These operations are unique to relational databases.

Relational databases support an important concept of dynamic views. In a relational database, a view is not a part of the physical schema, it is dynamic. Hence changing the data in a table alters the data depicted by the view. Views can subset data, join and simplify multiple relations, dynamically hide the complexity in the data and reduce the data storage requirements.

Relational databases use SQL, which is an easy and human-readable language. SQL instructions are in the form of plain instructions, which can be put to the database for implementation. Most of the database vendors support the SQL standard. Relational databases provide the users with simple operations to manipulate data in the databases and retrieve it. Moreover relational databases establish defined relationships between the tables, thus giving their users a complete picture of the data stored.

Relational databases have an excellent security. A relational database supports access permissions, which allow the database administrator to implement need-based permissions to the access of the data in database tables. Relational databases support the concept of users and user rights, thus meeting the security needs of databases. Relations are associated with privileges like create privilege, grant privilege, select, insert and delete privileges, which authorize different users for corresponding operations on the database. P a g e | 9 The other important advantages of relational databases include their performance, power, and support to new hardware technologies as also flexibility and a capacity to meet all types of data needs. Relational databases are scalable and provide support for the implementation of distributed systems.

Section 3 Logical and Physical Design (cont.) Data Dictionary Customers Attribute Data Type Primary Key Required Foreign Key Indexed Description Default Values Input Mask Customer ID Integer Yes Yes No Yes Customer id uniquely identifies each record in the customers table Should be explicitly defined Customer Last Name Varchar(20) No No No No Represents customers last name Null A - Z or a - z Customer First Name Varchar(20) No No No No Represents customers first name Null A-Z or a-z Business Name Varchar(30) No No No No Represents business name Null A-Z or a-z Business Address Varchar(40) No No No No Represents business address Null A-Z or a- z or 0-9 Business phone Varchar(10) No No No No Represents business phone Null 0-9 P a g e | 10 Section 3 Logical and Physical Design (cont.) Data Dictionary Salesman Attribute Data Type Primary Key Required Foreign Key Indexed Description Default Values Input Mask Salesman ID Integer Yes Yes No Yes salesman id uniquely identifies each record in the salesman table Should be explicitly defined Customer id Integer Yes Yes Yes Yes Customer id represents foreign key relationship in salesman table Look up from customers table Sales man Last Name Varchar(20) No No No No Represents salesman last name Null A-Z or a-z Salesman First Name Varchar(20) No No No No Represents salesman first name Null A-Z or a-z Salesman Address Varchar(50) No No No No Represents salesman address Null A-Z or a- z or 0-9 Salesman phone Varchar(10) No No No No Represents salesman phone Null 0-9 P a g e | 11 Section 3 Logical and Physical Design (cont.) Data Dictionary Machine Attribute Data Type Primary Key Required Foreign Key Indexed Description Default Values Input Mask Machine ID Integer Yes Yes No Yes Machine id uniquely identifies each record in the machine table Should be explicitly defined Machine brand Varchar(20) No No No No Represents machine brand Null A - Z or a - z Machine Model Varchar(20) No No No No Represents machine model Null A-Z or a-z Machine serial Varchar(30) No No No Yes Represents machine serial number Null A-Z or a- z or 0-9 Machine year Varchar(10) No No No No Represents machine year Null 0-9 Machine engine Varchar(20) No No No No Represents machine engine Null A-z P a g e | 12 Section 3 Logical and Physical Design (cont.) Data Dictionary Sales Attribute Data Type Primary Key Required Foreign Key Indexed Description Default Values Input Mask Sales ID Integer Yes Yes No Yes sales id uniquely identifies each record in the sales table Should be explicitly defined Customer id Integer Yes Yes Yes Yes Customer id represents foreign key relationship in sales table Look up from customers table Salesman ID Integer Yes Yes Yes Yes salesman id uniquely identifies each record in the sales table Look up from salesman table Machine ID Integer Yes Yes Yes Yes Machine id uniquely identifies each record in the sales table Look up from machine table Sale date Varchar(10) No Yes No No Represents sales date Null A-Z or a- z or 0-9 Trade in Varchar(10) No No No No Represents trade in currency Null 0-9 Sale-Price Money No Yes No No Represents sale price 0 0-9 P a g e | 13 Section 3 Logical and Physical Design (cont.) Data Dictionary Services Attribute Data Type Primary Key Required Foreign Key Indexed Description Default Values Input Mask Service ID Integer Yes Yes No Yes Service id uniquely identifies each record in the service table Should be explicitly defined 1, 2 Customer id Integer Yes Yes Yes Yes Customer id represents foreign key relationship in services table Look up from customers table Machine ID Integer Yes Yes Yes Yes Machine id represents foreign key relationship in services table Look up from machine table Service type Varchar(10) No Yes No No Represents service type Null A-Z or a- z or 0-9 Service tech Varchar(15) No No No No Represents service technician Null a-z Work order Varchar(50) No Yes No No Represents work order details Null a-z Service date Varchar(10) No Yes No No Represents service date Null 0-9 or A-z Service Cost Money No Yes No No Represents cost of the Null 0-9 P a g e | 14 service Section 3 Logical and Physical Design (cont.) Normalization CUST _ID BUS_N AME BUS_ ADD BUS_S TATE BUS_ CITY BUS_ ZIP BUS_P HONE SALESM AN_ID SALESM AN_ID SALESMAN_ LNAME SALESMAN _FNAME SALESMAN _PHONE 1000 Kokosi ng Constru ction 1516 Timke n Road OH Woost er 4469 1 330- 264- 1175 200 200 Brockman Carey 216-470-8743 1001 Shelly & Sands 3840 Durran t RD OH Zanesv ille 4370 1 740 - 453- 0721 201 201 Wade Tom 614-402-3687 1002 Shelly Compa ny 143R East Colum bus ST OH Thornv ille 4307 6 740- 246- 6315 202 202 McLean Don 330-655-5900 1003 Decker Constru ction 3040 McKin ley Ave OH Colum bus 4320 4 614- 488- 7958 203 203 McLean Scott 614-205-8312 1004 Comple te General Constru ction 1221 East 5th Avenu e OH Colum bus 4321 9 614- 258- 9515 204 204 Hattendorf Jim 330-416-3334 1005 Base 1595 OH Colum 4322 614- 205 205 Zink Greg 216-470-8745 P a g e | 15 Constru ction Frank Road bus 3 276 - 5501 1006 Chemco te 7599 Fishel North OH Dublin 4301 6 614 - 792- 2683 206 206 Sidwell Kirk 937-478-3595 1007 Barrett Paving 355 Cavett Ave OH Cincin nati 4521 5 513 - 200- 7811 207 207 Hersey Bart 216-470-8744 Section 3 Logical and Physical Design (cont.) A table is un-normalized if it contains redundant and insertion, update and deletion anomalies disturb the data integration. First I will discuss the above table for first normal form. First Normal Form CUST_ ID SALESMAN_ ID BUS_ NAME BUS _ ADDRESS BUS_ STATE BUS_ CITY BUS_ ZIP BUS_ PHONE SALESMAN_ LNAME SALESMAN_ FNAME SALESMAN_ PHONE A database design is said to be in first normal form if there is no repeating groups are present in any row. Repeated group m ean multiple entries in a sing 2nd Normal Form A table is not in second normal form if it contains partial functional dependencies. Partial dependencies mean that some non-key attributes in the table depends upon some part of the key. In this case sales man name, address and phone are non-key attributes that depends upon part of composite key, which is customer id and salesman id. To remove partial dependencies we will cut the non key attributes and their determinant in separate tables. So after removing functional dependencies the above table is divided int o the following tables.

P a g e | 16 Section 3 Logical and Physical Design (cont.) CUST_ ID SALESMAN_ ID BUS_ NAME BUS_ ADDRESS BUS_ STATE BUS_ CITY BUS_ ZIP BUS_ PHONE SALESMAN_ LNAME SALESMAN_ FNAME SALESMAN_ PHONE Partial dependencies CUST_ID BUS_NAME BUS_ADD BUS_STATE BUS_CITY BUS_ZIP BUS_PHONE 1000 Kokosing Construction 1516 Timken Road OH Wooster 44691 330-264-1175 1001 Shelly & Sands 3840 Durrant RD OH Zanesville 43701 740-453-0721 1002 Shelly Company 143R East Columbus ST OH Thornville 43076 740-246-6315 1003 Decker Construction 3040 McKinley Ave OH Columbus 43204 614-488-7958 1004 Complete General Construction 1221 East 5th Avenue OH Columbus 43219 614-258-9515 1005 Base Construction 1595 Frank OH Columbus 43223 614-276-5501 P a g e | 17 Road 1006 Chemcote 7599 Fishel North OH Dublin 43016 614-792-2683 1007 Barrett Paving 355 Cavett Ave OH Cincinnati 45215 513-200-7811 Section 3 Logical and Physical Design (cont.) SALESMAN_ID SALESMAN_ID SALESMAN_LNAME SALESMAN_FNAME SALESMAN_PHONE 200 200 Brockman Carey 216-470-8743 201 201 Wade Tom 614-402-3687 202 202 McLean Don 330-655-5900 203 203 McLean Scott 614-205-8312 204 204 Hattendorf Jim 330-416-3334 205 205 Zink Greg 216-470-8745 206 206 Sidwell Kirk 937-478-3595 207 207 Hersey Bart 216-470-8744 3 rd Normal Form A table is in 3 rd normal form if it does not contain any transitive dependencies. A table contains transitive dependencies if any non-key attribute is determined by one or more non key attributes. Analyzing the above tables for transitive dependencies we find that there is no such transitive dependency so the tables are already in third normal form. P a g e | 18 CUST_ ID SALESMAN_ ID BUS_ NAME BUS_ ADDRESS BUS_ STATE BUS_ CITY BUS_ ZIP BUS_ PHONE SALESMAN_ LNAME SALESMAN_ FNAME SALESMAN_ PHONE Section 3 Logical and Physical Design (cont.) 2nd set of tables Now I will be applying the same process to the remaining table as follows. SER VIC E_I D MA CH _ID CU ST _I D MAC H_B RAN D MAC H_M ODE L MAC H_SE RIAL MA CH_ YEA R CU ST _I D SERV ICE_ TYPE SERV ICE_ TECH SERV ICE_ DATE SERV ICE_ COST SA LE S_I D SALE SMA N_ID CU ST _I D MA CH _ID SAL ES_ DAT E TR AD E_I N SAL E_P RIC E W30 00 M1 000 100 0 LEEB OY 8515 B 45789 2008 100 0 CHA RGE RICK 2 - Apr- 10 3458. 00 C20 00 200 100 0 M1 000 11 - Apr- 10 540 0.00 8400 0.00 W30 01 M1 001 100 1 WAC KER RD11 A 58794 5 2009 100 1 CHA RGE DEV ON 12- May- 09 1650. 00 C20 01 201 100 1 M1 001 14- Mar- 09 165 0.00 2800 0.00 W30 02 M1 002 100 2 LEEB OY 400B 249 2008 100 2 WAR RAN TY RICK 22- Jun- 10 1865. 32 C20 02 202 100 2 M1 002 22- Jun- 09 189 5.00 2700 0.00 W30 03 M1 003 100 3 HYU NDAI HL75 0TM- 9 EU45 H008 9 2010 100 3 VEN DOR BOB 18- Dec- 09 8453. 69 C20 03 203 100 3 M1 003 20- Jul- 08 140 0.00 1240 00.0 0 W30 04 M1 004 100 4 VOG ELE 5200- 2 77400 65 2010 100 4 CHA RGE JOHN 27- Feb- 11 2200. 00 C20 04 204 100 4 M1 004 17- Dec- 10 240 00.0 0 2540 00.0 0 W30 05 M1 005 100 5 WIR TGE N W190 0 06.20. 0758 2011 100 5 WAR RAN Y DEV ON 14- Jan-11 1850. 00 C20 05 205 100 5 M1 005 30- FEB- 10 800 0.00 4640 00.0 0 P a g e | 19 W30 06 M1 006 100 6 LEEB OY 685B 45895 2007 100 6 WAR RAN TY RICK 26 - Feb- 11 856.6 8 C20 06 206 100 6 M1 006 18 - Sep- 09 660 0.00 5600 0.00 W30 07 M1 007 100 7 WIR TGE N W250 0S 04.W R.025 6 2009 100 7 CHA RGE DEV ON 11 - Mar- 11 1200. 00 C20 07 207 100 7 M1 007 26 - Nov- 07 427 5.00 1850 00.0 0 Section 3 Logical and Physical Design (cont.) First Normal Form A database design is said to be in first normal form if there is no repeating groups are present in any row. Repeated group m ean ntries in the above table so the table is in first normal form. 2nd Normal Form A table is not in second normal form if it contains partial functional dependencies. Partial dependencies mean that some non-key attributes in the table depends upon some part of the key. In this case I have find out the following partial dependencies:

SER VIC E_I D MA CH _ID CU ST _I D MAC H_BR AND MAC H_M ODE L MAC H_SE RIAL MAC H_Y EAR CU ST _I D SERV ICE_ TYPE SERV ICE_ TECH SERV ICE_ DATE SERV ICE_ COST SA LE S_I D SALE SMA N_ID CU ST _I D MA CH _ID SAL ES_ DAT E TR AD E_I N SAL E_P RIC E P a g e | 20 Section 3 Logical and Physical Design (cont.) Partial Dependencies To remove partial dependencies we will cut the non key attributes and their determinant in separate tables. So after removing functional dependencies the above table is divided into the following tables. MACH_ID MACH_BRAND MACH_MODEL MACH_SERIAL MACH_YEAR M1000 LEEBOY 8515B 45789 2008 M1001 WACKER RD11A 587945 2009 M1002 LEEBOY 400B 249 2008 M1003 HYUNDAI HL750TM-9 EU45H0089 2010 M1004 VOGELE 5200-2 7740065 2010 M1005 WIRTGEN W1900 06.20.0758 2011 M1006 LEEBOY 685B 45895 2007 M1007 WIRTGEN W2500S 04.WR.0256 2009 SERVI CE_ID MAC H_ID CUS T_ID SERVICE _TYPE SERVICE _TECH SERVICE _DATE SERVICE _COST SALESM AN_ID CUS T_ID MAC H_ID SALES_ DATE TRAD E_IN SALE_P RICE W3000 M100 0 1000 CHARGE RICK 2-Apr-10 3458.00 200 1000 M100 0 11-Apr- 10 5400.0 0 84000.0 0 W3001 M100 1 1001 CHARGE DEVON 12-May-09 1650.00 201 1001 M100 1 14-Mar- 09 1650.0 0 28000.0 0 W3002 M100 2 1002 WARRA NTY RICK 22-Jun-10 1865.32 202 1002 M100 2 22-Jun- 09 1895.0 0 27000.0 0 SER VIC E_I D MA CH _ID CU ST _I D MAC H_BR AND MAC H_M ODE L MAC H_SE RIAL MAC H_Y EAR CU ST _I D SERV ICE_ TYPE SERV ICE_ TECH SERV ICE_ DATE SERV ICE_ COST SA LE S_I D SALE SMA N_ID CU ST _I D MA CH _ID SAL ES_ DAT E TR AD E_I N SAL E_P RIC E P a g e | 21 W3003 M100 3 1003 VENDOR BOB 18-Dec-09 8453.69 203 1003 M100 3 20 - Jul - 08 1400.0 0 124000. 00 W3004 M100 4 1004 CHARGE JOHN 27-Feb-11 2200.00 204 1004 M100 4 17 - Dec - 10 24000. 00 254000. 00 W3005 M100 5 1005 WARRA NY DEVON 14-Jan-11 1850.00 205 1005 M100 5 30 - FEB - 10 8000.0 0 464000. 00 W3006 M100 6 1006 WARRA NTY RICK 26-Feb-11 856.68 206 1006 M100 6 18 - Sep - 09 6600.0 0 56000.0 0 W3007 M100 7 1007 CHARGE DEVON 11-Mar-11 1200.00 207 1007 M100 7 26 - Nov - 07 4275.0 0 185000. 00 3 rd Normal Form A table is in 3 rd normal form if it does not contain any transitive dependencies. A table contains transitive dependencies if any non-key attribute is determined by one or more non key attributes. Analyzing the above tables for transitive dependencies we find that there are transitive dependencies as follows:

Transitive dependency So after removing this dependency we get the following tables SERVICE_ID MACH_ID CUST_ID SERVICE_TYPE SERVICE_TECH SERVICE_DATE SERVICE_COST W3000 M1000 1000 CHARGE RICK 2-Apr-10 3458.00 SERVICE_I D MACH_I D CUST_I D SERVICE _ TYPE SERVICE _ TECH SERVIC E _DATE SERVICE _ COST SALE _ ID SALESMA N _ID SALES _ DATE TRAD E _IN SALE _ PRICE P a g e | 22 W3001 M1001 1001 CHARGE DEVON 12-May-09 1650.00 W3002 M1002 1002 WARRANTY RICK 22-Jun-10 1865.32 W3003 M1003 1003 VENDOR BOB 18-Dec-09 8453.69 W3004 M1004 1004 CHARGE JOHN 27-Feb-11 2200.00 W3005 M1005 1005 WARRANY DEVON 14-Jan-11 1850.00 W3006 M1006 1006 WARRANTY RICK 26-Feb-11 856.68 W3007 M1007 1007 CHARGE DEVON 11-Mar-11 1200.00 SALES_ID SALESMAN_ID CUST_ID MACH_ID SALES_DATE TRADE_IN SALE_PRICE C2000 200 1000 M1000 11-Apr-10 5400.00 84000.00 C2001 201 1001 M1001 14-Mar-09 1650.00 28000.00 C2002 202 1002 M1002 22-Jun-09 1895.00 27000.00 C2003 203 1003 M1003 20-Jul-08 1400.00 124000.00 C2004 204 1004 M1004 17-Dec-10 24000.00 254000.00 C2005 205 1005 M1005 30-FEB-10 8000.00 464000.00 C2006 206 1006 M1006 18-Sep-09 6600.00 56000.00 CUST_ID BUS_NAME BUS_ADD BUS_STATE BUS_CITY BUS_ZIP BUS_PHONE 1000 Kokosing Construction 1516 Timken Road OH Wooster 44691 330-264-1175 1001 Shelly & Sands 3840 Durrant RD OH Zanesville 43701 740-453-0721 1002 Shelly Company 143R East Columbus ST OH Thornville 43076 740-246-6315 1003 Decker Construction 3040 McKinley Ave OH Columbus 43204 614-488-7958 1004 Complete General Construction 1221 East 5th Avenue OH Columbus 43219 614-258-9515 1005 Base Construction 1595 Frank Road OH Columbus 43223 614-276-5501 P a g e | 23 1006 Chemcote 7599 Fishel North OH Dublin 43016 614-792-2683 1007 Barrett Paving 355 Cavett Ave OH Cincinnati 45215 513-200-7811 Section 3 Logical and Physical Design (cont.) SALESMAN_ID SALESMAN_ID SALESMAN_LNAME SALESMAN_FNAME SALESMAN_PHONE 200 200 Brockman Carey 216-470-8743 201 201 Wade Tom 614-402-3687 202 202 McLean Don 330-655-5900 203 203 McLean Scott 614-205-8312 204 204 Hattendorf Jim 330-416-3334 205 205 Zink Greg 216-470-8745 206 206 Sidwell Kirk 937-478-3595 207 207 Hersey Bart 216-470-8744 MACH_ID MACH_BRAND MACH_MODEL MACH_SERIAL MACH_YEAR M1000 LEEBOY 8515B 45789 2008 M1001 WACKER RD11A 587945 2009 M1002 LEEBOY 400B 249 2008 M1003 HYUNDAI HL750TM-9 EU45H0089 2010 M1004 VOGELE 5200-2 7740065 2010 M1005 WIRTGEN W1900 06.20.0758 2011 M1006 LEEBOY 685B 45895 2007 M1007 WIRTGEN W2500S 04.WR.0256 2009 P a g e | 24 Section 3 Logical and Physical Design (cont.) De-Normalization De-normalization is the reverse process of Normalization i.e., to combine two or more tables into a single table. De-normalization increases the performance (Searching data from one table is quiet faster than searching data from multiple tables). Good for OLAP systems. As we are going to design OLTP system database so de-normalization will cause a huge increase in data redundancy, and maintenance issues. So we should avoid de-normalization in OLTP systems. Benefits to the system The implementation of this database design will improve the performance of the system while reducing the time to query the data. As the database is fully normalized the system will provide the following performance features Searching, sorting, and creating indexes is faster, since tables are narrower, and more rows fit on a data page. You usually have more tables. Index searching is often faster, since indexes tend to be narrower and shorter. More tables allow better use of segments to control physical placement of data. You usually have fewer indexes per table, so data modification commands are faster. Fewer null values and less redundant data, making your database more compact. P a g e | 25 Triggers execute more quickly if you are not maintaining redundant data. Data modification anomalies are reduced. Normalization is conceptually cleaner and easier to maintain and change as your needs change. While fully normalized databases require more joins, joins are generally very fast if indexes are available on the join columns.

Adaptive Server is optimized to keep higher levels of the index in cache, so each join performs only one or two physical I/Os for each matching row. The cost of finding rows already in the data cache is extremely low.