6

Running head: CREATING A DATABASE IN SQL 1

CREATING A DATABASE IN SQL 15

Creating a Database in SQL

Chioma Nnama

ISM 641 – Database Design and Management

Asif Choudhery

October 6, 2019

Creating a Database in SQL

1. Entity Relationship Diagram

EMPLOYEE EMP_NUM

EMP_FNAME EMP_INITIAL EMP_HIREDATE JOB_CODE

PROJECT PROJ_NUM

PROJ_NAME PROJ_VALUE PROJ_BALANCE

EMP_NUM

ASSIGNMENT ASIGN_NUM

ASSIGN_DATE ASSIGN_HOURS ASSIGN_CHG_HOUR ASSIGN_CHARGE

EMP_NUM

JOB JOB_CODE

JOB_DESCRIPTION JOB_CHG JOB_HOUR

Creating Entity Relationships and their Attributes

The first step towards developing an SQL database is to create an entity relationship model, which acts as a conceptual depiction of the database. As indicated in the above figure, the entity relationship model is made up of entities, attributes, as well as the relationships. An entity represents a real-world depiction of an object, such as employees, project, as well as assignments. Each of the entities has characteristics, which are normally referred to as attributes (Nielsen & Parui, 2011). For instance, the key attributes for employees may include employee ID, as well as employee name. Another critical component of the entity relationship model is relationship, which acts as a link between two or more tables. Once the entity relationship model is complete, it is important to convert it into a database structure. More precisely, a new table should be established to represent each entity, and the table should contain as many columns as attributes within the entity. In addition, the table should be developed to represent each many-to-many relationship.

a. Steps of Creating the JOB table

CREATE TABLE JOB ( JOB_CODE VARCHAR(3), JOB_DESCRIPTION VARCHAR(25), JOB_CHG_HOUR FLOAT(8), JOB_LAST_UPDATE DATETIME, PRIMARY KEY(JOB_CODE) ); INSERT INTO JOB VALUES('500','Programmer', '35.75','2015-11-20'); INSERT INTO JOB VALUES('501','Systems Analyst', '96.75','2015-11-20'); INSERT INTO JOB VALUES('502','Database Designer', '125', '2016-3-24'); INSERT INTO JOB VALUES('503','Electrical Engineer', '84.5', '2015-11-20'); INSERT INTO JOB VALUES('504','Mechanical Engineer', '67.9', '2015-11-20'); INSERT INTO JOB VALUES('505','Civil Engineer', '55.78','2015-11-20'); INSERT INTO JOB VALUES('506','Clerical Support', '26.87','2015-11-20'); INSERT INTO JOB VALUES('507','DSS Analyst', '45.95','2015-11-20'); INSERT INTO JOB VALUES('508','Applications Designer','48.1', '2016-3-24'); INSERT INTO JOB VALUES('509','Bio Technician', '34.55','2015-11-20'); INSERT INTO JOB VALUES('510','General Support', '18.36','2015-11-20');

/* -- */

b. Steps of creating the EMPLOYEE table

CREATE TABLE EMPLOYEE ( EMP_NUM VARCHAR(3), EMP_LNAME VARCHAR(15), EMP_FNAME VARCHAR(15), EMP_INITIAL VARCHAR(1), EMP_HIREDATE DATETIME, JOB_CODE VARCHAR(3), EMP_YEARS INT(3), PRIMARY KEY(EMP_NUM), FOREIGN KEY (JOB_CODE) REFERENCES JOB(JOB_CODE) ); INSERT INTO EMPLOYEE VALUES('101','News','John','G','2000-11-8','502','4'); INSERT INTO EMPLOYEE VALUES('102','Senior','David','H','1989-7-12','501','15'); INSERT INTO EMPLOYEE VALUES('103','Arbough','June','E','1996-12-1','503','8'); INSERT INTO EMPLOYEE VALUES('104','Ramoras','Anne','K','1987-11-15','501','17'); INSERT INTO EMPLOYEE VALUES('105','Johnson','Alice','K','1993-2-1','502','12'); INSERT INTO EMPLOYEE VALUES('106','Smithfield','William',NULL,'2004-6-22','500','0'); INSERT INTO EMPLOYEE VALUES('107','Alonzo','Maria','D','1993-10-10','500','11'); INSERT INTO EMPLOYEE VALUES('108','Washington','Ralph','B','1991-8-22','501','13'); INSERT INTO EMPLOYEE VALUES('109','Smith','Larry','W','1997-7-18','501','7'); INSERT INTO EMPLOYEE VALUES('110','Olenko','Gerald','A','1995-12-11','505','9'); INSERT INTO EMPLOYEE VALUES('111','Wabash','Geoff','B','1991-4-4','506','14'); INSERT INTO EMPLOYEE VALUES('112','Smithson','Darlene','M','1994-10-23','507','10'); INSERT INTO EMPLOYEE VALUES('113','Joenbrood','Delbert','K','1996-11-15','508','8'); INSERT INTO EMPLOYEE VALUES('114','Jones','Annelise',NULL,'1993-8-20','508','11'); INSERT INTO EMPLOYEE VALUES('115','Bawangi','Travis','B','1992-1-25','501','13'); INSERT INTO EMPLOYEE VALUES('116','Pratt','Gerald','L','1997-3-5','510','8'); INSERT INTO EMPLOYEE VALUES('117','Williamson','Angie','H','1996-6-19','509','8'); INSERT INTO EMPLOYEE VALUES('118','Frommer','James','J','2005-1-4','510','0');

/* -- */

c. Creating PROJECT Table

CREATE TABLE PROJECT ( PROJ_NUM VARCHAR(3), PROJ_NAME VARCHAR(25), PROJ_VALUE FLOAT(8), PROJ_BALANCE FLOAT(8), EMP_NUM VARCHAR(3), PRIMARY KEY(PROJ_NUM), FOREIGN KEY(EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM) ); INSERT INTO PROJECT VALUES('15','Evergreen','1453500','1002350','103'); INSERT INTO PROJECT VALUES('18','Amber Wave','3500500','2110346','108'); INSERT INTO PROJECT VALUES('22','Rolling Tide','805000','500345.2','102'); INSERT INTO PROJECT VALUES('25','Starflight','2650500','2309880','107');

/* -- */

d. Creating ASSIGNMENT Table

CREATE TABLE ASSIGNMENT ( ASSIGN_NUM INT(5), ASSIGN_DATE DATETIME, PROJ_NUM VARCHAR(3), EMP_NUM VARCHAR(3), ASSIGN_JOB VARCHAR(3), ASSIGN_CHG_HR FLOAT(8), ASSIGN_HOURS FLOAT(8), ASSIGN_CHARGE FLOAT(8), PRIMARY KEY (ASSIGN_NUM), FOREIGN KEY (PROJ_NUM) REFERENCES PROJECT(PROJ_NUM), FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM) ); INSERT INTO ASSIGNMENT VALUES('1001','2016-3-22','18','103','503','84.5','3.5','295.75'); INSERT INTO ASSIGNMENT VALUES('1002','2016-3-22','22','117','509','34.55','4.2','145.11'); INSERT INTO ASSIGNMENT VALUES('1003','2016-3-22','18','117','509','34.55','2','69.10'); INSERT INTO ASSIGNMENT VALUES('1004','2016-3-22','18','103','503','84.5','5.9','498.55'); INSERT INTO ASSIGNMENT VALUES('1005','2016-3-22','25','108','501','96.75','2.2','212.85'); INSERT INTO ASSIGNMENT VALUES('1006','2016-3-22','22','104','501','96.75','4.2','406.35'); INSERT INTO ASSIGNMENT VALUES('1007','2016-3-22','25','113','508','50.75','3.8','192.85'); INSERT INTO ASSIGNMENT VALUES('1008','2016-3-22','18','103','503','84.5','0.9','76.05'); INSERT INTO ASSIGNMENT VALUES('1009','2016-3-23','15','115','501','96.75','5.6','541.80'); INSERT INTO ASSIGNMENT VALUES('1010','2016-3-23','15','117','509','34.55','2.4','82.92'); INSERT INTO ASSIGNMENT VALUES('1011','2016-3-23','25','105','502','105','4.3','451.5'); INSERT INTO ASSIGNMENT VALUES('1012','2016-3-23','18','108','501','96.75','3.4','328.95'); INSERT INTO ASSIGNMENT VALUES('1013','2016-3-23','25','115','501','96.75','2','193.5'); INSERT INTO ASSIGNMENT VALUES('1014','2016-3-23','22','104','501','96.75','2.8','270.9'); INSERT INTO ASSIGNMENT VALUES('1015','2016-3-23','15','103','503','84.5','6.1','515.45'); INSERT INTO ASSIGNMENT VALUES('1016','2016-3-23','22','105','502','105','4.7','493.5'); INSERT INTO ASSIGNMENT VALUES('1017','2016-3-23','18','117','509','34.55','3.8','131.29'); INSERT INTO ASSIGNMENT VALUES('1018','2016-3-23','25','117','509','34.55','2.2','76.01'); INSERT INTO ASSIGNMENT VALUES('1019','2016-3-24','25','104','501','110.5','4.9','541.45'); INSERT INTO ASSIGNMENT VALUES('1020','2016-3-24','15','101','502','125','3.1','387.5'); INSERT INTO ASSIGNMENT VALUES('1021','2016-3-24','22','108','501','110.5','2.7','298.35'); INSERT INTO ASSIGNMENT VALUES('1022','2016-3-24','22','115','501','110.5','4.9','541.45'); INSERT INTO ASSIGNMENT VALUES('1023','2016-3-24','22','105','502','125','3.5','437.5'); INSERT INTO ASSIGNMENT VALUES('1024','2016-3-24','15','103','503','84.5','3.3','278.85'); INSERT INTO ASSIGNMENT VALUES('1025','2016-3-24','18','117','509','34.55','4.2','145.11');

/* -- */

e. Creating JOB Table

2. Appropriate Data Types for All Fields

Each of the fields has their own appropriate data types. To this end, the organization’s database system accommodates different types of data. The data types that the organization supports include character, numeric, as well as date. Examples of numeric data types include: numbers, integers, small integers (SMALLINT), as well as decimal. The declaration number suggests that the numbers will be kept in form of two decimal places, which could span to even seven digits in length. The integer data type is also commonly abbreviated as INT (Coronel & Morris, 2016). This data type is a whole counting number, which is founded on the idea that they cannot be utilized if the organization seeks to store numbers that need decimal places. Furthermore, SMALLINT resembles integers. However, they are restricted to integer values of up to six digits. In case the integer values are significantly smaller, SMALLINT are often utilized instead of INT. Furthermore, character, also abbreviated as CHAR, are fixed length character data types whose lengths reach up to 255 characters. In case strings that are less long than the CHAR parameter value are utilized, then the remaining spaces are often rendered unused (Coronel & Morris, 2016). Finally, the DATE data type has been used by the organization’s database system to store dates in the Julian data format. The company considers character data types as those that include both fixed-length and variable-length character data. A variable-length character data type, such as the VARCHAR, is essentially specified within a maximum length.

3. Translating Business Requirements into Integrity Constraints

Integrity constraints have been used in the database system to ensure that the database remains consistent. Integrity constraints act as forms of restrictions that guarantee consistency. To this end, a database is regarded to be consistent in case the database meets a collection of constraints (Ray, 2008). The task of maintaining database consistency entails utilization of various DBMS modules, which may include concurrency control (Ray, 2008). In this case, the organization’s database has been made consistent with the utilization of recovery management strategies, protection and authorization control, as well as semantic integrity control measures. The primary purpose for which integrity controls have been established is to verify if the updates that have been undertaken by the SQL application program contravene the consistency of the states of each of the database systems. In the vent that such violations are detected, the integrity control module performs the task of rejecting the updates or undertakes some actions in order to compensate the impacts of the updates.

References

Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management.

New York: Cengage Learning.

Nielsen, P., & Parui, U. (2011). Microsoft SQL server 2008 bible (Vol. 607). New Jersey: John

Wiley & Sons.

Ray, C. (2008). Distributed database systems. London: Pearson Education.