atabase design stage is one of the important phases in the information system development that will determine the success and the quality of the database application or the finish product. Although the database can be visible during the analysis stage of the information system development (Plan, Analyze, Design and Implement), in this case we are analyzing in the reverse manner, which the database application development process already completed. We will try to apply all the techniques and rules that normally used during the design stage.
ACTIVITY
So, in this activity, you and your group members are required to analyze a web-based application called Court Reservation System (CRS) own and managed by UUM University. The interfaces (screenshots) of the system are shown in the following section. Based on the given information, all groups have to submit the following completed tasks:
Guidelines:
The following screenshots are the web interfaces for CRS system.
 |
CONTENTS
BASIC ANALYSIS
BUILDING THE ERD DIAGRAM
CREATING THE RELATIONAL MODEL
BUILDING DATABASE MODEL USING MySQL AND DBDESIGNER 4
EXECUTING MySQL SCRIPTS USING DBDESIGNER 4
CREATING DATABASE MODEL (REVERSE ENGINEERING THE DATABASE TABLES)
INSERTING SAMPLE REAL DATA
VIEWING THE TABLES DATA
USING SQL DML AGAINST THE DATABASE
CONCLUSION
REFERENCES
BASIC ANALYSIS
We start by analyzing the web site based on the observation of the form fields, the data that need to be filled in and the web pages. We found that there are several issues need to be sorted out and summarized the main issues as follows:
- If we based on just the matric number, only student can make reservations. What about UUM staff and outsiders that don’t have matric number?
- If we restrict the reservation for the student only, then the occupation column can be dropped because all or majority should be a student. Furthermore the IC number (similar to SSN number) may be NULL for foreign students.
- If we open to the outsider, then the IC number must be included and matric number cannot be used as a candidate key. However foreign students still can fill in the IC numbers.
- Foreign student cannot fill in the IC number column instead they may fill in Passport number that needs an extra column.
- By assuming the system only for UUM student, then the matric number and email address can be candidate keys and the system should be rename to "UUM Student Only Court Reservation System". In this case we can drop the occupation and IC columns; because all students supposed to be a student (full or part timer) and foreign student doesn’t have IC number or we need to permit NULL values for IC Number column. Outsiders and staff need to do all the reservation task manually making the CRS not so usable.
- By assuming the CRS is for all (student, staff and outsiders), the matric number cannot be used as a candidate key. In this case we need another attribute to be considered as candidate key such as UserID. Without providing another column for Passport number for foreign students, we can share it with IC number column. However this obviously violates the 1NF, the atomicity of the cell. Outsiders also may not have email addresses. In this case also the users may have generalization entity in the ERD diagram.
At this stage we try to use the Normal Form rules whenever possible. We suggest a name to a table, determine the entity types, attributes and then populate some sample data into the table.By analyzing the New Registration web page, the following is the Student entity type with attributes. The matricNo and emailAddr can be candidate keys.Student matricNo emailAddr name gender doc race contactNo address psword The following is the Student table populated with sample data. At first glance, the race and gender look redundant in this table however this non keys information is not significant here and does not create update anomalies as a reason to provide separate tables. For example, gender only consists of two values of data and the race consists of several values of data only. These values of data will not change forever. However, when we need to edit the value of male or female there will be a lot of records to be updated. We will re-analyze this issue later after building the ERD diagram if needed. This table is linked to the Payment table. Only student that already made payment can place their reservation.StudentmatricNoemailAddrnamegenderdobracecontactNoaddresspsword86778saad@uum.edu.mySaad bin alimale23-10-1970Malay012-3452321123 Kolej Maybank, UUM1234&$89001maidah@uum.edu.myMaidah bt osmanfemale10-02-1980Malay013-6753412214 Kolej Guthrie, UUM5%$#@185789salmi@uum.edu.mySalmi bt khalidfemale12-07-1976Malay016-6542378201 Kolej Proton, UUMyu65#$84521fong_32@yahoo.comFong Ah Kuifemale25-09-1978Chinese019-3823419321 Kolej Maybank, UUMh8$&j#87991santini@gmail.comSantini a/p Kalupafemale21-01-1960Indian016-7682314103 Kolej Guthrie, UUMk6y&$*89667salman@yahoo.comSalman b. Ghani Khanmale04-05-1969Malay013-6752312103 Kolej Proton, UUMrt5$&887669fakhrul@uum.edu.myFakhrul ibni Sayutimale17-01-1972Other017-3416745121 Kolej Maybank, UUM7u5r%@The following is a payment table and a sample data. The ccNumber can be a candidate key and this table is linked to the Reservation table, once payment has been made, the student can place a reservation.PaymentccNumberccNameccTypeccExpiryDateccIssuerBank5422240032001745Sarip Abdul bin GhaniVISA03/2009Maybank3400220043007896Saripah bt MalikMaster10/2010CIMB Bank2433340022005674Hamzah bin IsmailVISA12/2009Public Bank3400240033223452Irene Khoh EngMaster07/2010AMM Bank5600340022331233Satiya a/l ManiamVISA02/2010RHB Bank4500230034002314Ong Ka TingMaster05/2012Bank IslamThe reservation made (courtType, courtNo, reserveDate, timeStart and timeEnd) can be indicated by a status attribute. The status will be used to set the availability of the court on the certain time and date. The Reservation web page is similar to the Cancel Reservation page so the cancellation can be done using the UPDATE command on the status of the reservation. The reservation is linked to the Court and CourtTimeTable information. Just with the one attribute, the Reservation may be a relationship with an attribute of the Court and CourtTimeTable.ReservationstatusavailablebookedThe Court Table web page contains information on court type, day and time schedules and court number. We separate these three information into two entity types: CourtTimeTable and Court where Court will hold court type and court number. The tables have been separated to avoid update anomalies. We divided the time column into two columns to avoid the 1NF violation.The following is a sample time and date data provided for the CourtTimeTable table. From the web pages, the CourtTimeTable is linked to the Court, Reservation and Equipment tables. We can see that the relationship between Court and CourtTimeTable is many-to-many (M-N).CourtTimeTablereserveDatetimeStarttimeEnd05/08/200708:00 am10.00 am05/08/200710.00 am12.00 pm05/08/200704.00 pm06.00 pm05/08/200706.00 pm08.00 pm05/08/200708.00 pm10.00 pmThe following is a Court table and a sample data. This table is linked to Reservation and CourtTimeTable tables.CourtcourtTypecourtNoBadmintonATennisABadmintonCSepak takrawCTennisBSquashBPing pongCThe following is an equipment table and a sample data. This table is linked to the CourtTimeTable and Court tables (courtType) because every courtType will have their own specific equipment type.EquipmentequipTypeequipQtyping pong bat2badminton rackets4tennis rackets4tennis ball10takraw ball4ping pong ball10The About Us and Term and Condition web pages just contain static html texts and the Report page contains derived attributes that we can ignore it.BUILDING THE ERD DIAGRAMIn this database design reverse engineer stage we will try to create an ERD diagram and refining the related things from the previous created entity types. We are assuming that this CRS is for UUM students only. The matric number and email address can be candidate keys. The IC number column can be removed else NULL value must be permitted or we can retain the IC number column with extra Passport Number column and both must enable NULL values. Matric number will be used as login username.Upon completion of the registration, student must make payment before permitting the reservation. One student can make many payments for different reservations.
After payment, one user can make many reservations. It is obvious when there is a tournament or competition.
One reservation may book many courts and at different time schedules. There are many court types and many time schedules. When combining the time and date, there are even many more time schedules. This M-N relationship with an attribute can be converted to two 1-M relationships and the conversion process will be shown in next section. The Reservation contains date, time, court type and court number. This obviously resembles the CourtTimeTable and Court attributes.
One court type of course has many types of equipment.
The following is an ERD diagram of our first attempt.
CREATING THE RELATIONAL MODELFrom the ERD diagram we try converting the previously created ERD to Relational Model. The following are the relational model for the previously created ERD diagram.Student(matricNo, emailAddr, name, gender, dob, race, contactNo, address, psword)
Primary Key matricNo
Foreign Key -
Payment(ccNumber, ccName, ccType, ccExpiryDate, ccIssuerBank, amount, matricNo)
Primary Key ccNumber
Foreign Key matricNo References Student
CourtTimeTable(reserveID, reserveDate, timeStart, timeEnd, ccNumber)
Primary Key reserveID
Foreign Key ccNumber References Payment
Court(courtID, courtType, courtNo)
Primary Key courtID
Foreign Key -
Reservation(reserveID, courtID, status)
Primary Key reserveID, courtID
Foreign Key reserveID References CourtTimeTable
Foreign Key courtID References Court
Equipment(equipNo, equipType, equipQty, courtID)
Primary Key equipNo
Foreign Key courtID References Court
BUILDING DATABASE MODEL USING MySQL AND DBDESIGNER 4Next, we convert the relational model to MySQL scripts. First of all we create a database named crssystemdb using MySQL Command Line Client.
Figure 16Then for the rest of the activities we use DBDesigner 4. Using DBDesigner 4 we connect to the empty crssystemdb database and in the Design Mode we start designing the tables and creating the relationship for the database model. Some screen snapshots for these tasks are shown in the following Figures.
Figure 7
Figure 8Make sure the DBDesigner was connected to the database as can be seen at the bottom right of the DBDesigner window.
Figure 9To create a new table, select and drop or click the table icon (shown below) and click on the editor designer.
Figure 10Next, we can start designing the table model.
Figure 11Select and right-click the table. Select the Edit Object context menu.
Figure 12The following is the Table Editor page. We fill in all the Student table’s information.
Figure 13The following Figure shows a completed Student table model.
Figure 14The table model’s properties also can be accessed from the Model window shown in the following Figure.
Figure 15The following Figure shows the Payment table properties.
Figure 16Next, we are ready to create a relationship (1-M) between the Student and Payment tables.
Figure 17Select, drag and drop the 1-M icon between the tables as shown in the following Figure.
Figure 18
Figure 19EXECUTING MySQL SCRIPTS USING DBDESIGNER 4After completing these two table models, we open the Query Mode and execute the remaining MySQL scripts to create a database model. Switching to the Query Mode, we execute the MySQL scripts (these scripts generated from the relational model). We found this is easier and faster compared to building the model in the Design Mode. We have to make sure there are no error(s) generated for all the tasks. At this stage, we must take care the order of the tables to be generated.CREATE TABLE Student(matricNo INTEGER(10),emailAddr VARCHAR(30),name VARCHAR(50),gender CHAR(10),dob DATE,race VARCHAR(20),contactNo VARCHAR(15),address VARCHAR(50),psword VARCHAR(10),CONSTRAINT PKmatricNo PRIMARY KEY (matricNo)) type=innodb;CREATE TABLE Payment(ccNumber BIGINT(16),ccName VARCHAR(30),ccType VARCHAR(20),ccExpiryDate VARCHAR(7),ccIssuerBank VARCHAR(30),matricNo INTEGER(10),amount DOUBLE(10,2),CONSTRAINT PKccNumber PRIMARY KEY (ccNumber),CONSTRAINT FKmatricNo FOREIGN KEY (matricNo) REFERENCES Student(matricNo))type=innodb;CREATE TABLE CourtTimeTable(reserveID BIGINT(10) AUTO_INCREMENT,reserveDate DATE,timeStart TIME,timeEnd TIME,ccNumber BIGINT(16),CONSTRAINT PKreserveID PRIMARY KEY (reserveID),CONSTRAINT FKccNumber FOREIGN KEY (ccNumber) REFERENCES Payment(ccNumber))type=innodb;CREATE TABLE Court(courtID BIGINT(10) AUTO_INCREMENT,courtType VARCHAR(40),courtNo CHAR(2),CONSTRAINT PKcourtID PRIMARY KEY (courtID))type=innodb;CREATE TABLE Reservation(reserveID BIGINT(10) AUTO_INCREMENT,courtID BIGINT(10),status VARCHAR(20),CONSTRAINT PKreservation PRIMARY KEY (reserveID, courtID),CONSTRAINT FKreserveID FOREIGN KEY (reserveID) REFERENCES CourtTimeTable(reserveID),CONSTRAINT FKcourtID FOREIGN KEY (courtID) REFERENCES Court(courtID))type=innodb;CREATE TABLE Equipment(equipNo BIGINT(10) AUTO_INCREMENT,equipType VARCHAR(40),equipQty BIGINT(10),courtID BIGINT(10),CONSTRAINT PKequipNo PRIMARY KEY (equipNo),CONSTRAINT FKcourtID1 FOREIGN KEY (courtID) REFERENCES Court(courtID))type=innodb;Firstly we switch to Query Mode.
Figure 20Next we copy and paste the SQL script into the editor and then click the Execute (
) button. We repeat this task for all the remaining tables.
Figure 21CREATING DATABASE MODEL (REVERSE ENGINEERING THE DATABASE TABLES)After completing all the table creation, we reverse engineer those tables generating the database model.
Figure 22
Figure 23In the Reverse Engineer window, we do the following tasks.- Select all the tables in the Tables list by selecting the tick boxes.
- Select the Build Relation tick box and the Build Relations based on Primary Keys.
- Select the Use Datatype substitution to convert all non standard data types.
There will be message box complaining errors, if there are any error(s) such as inconsistencies.
Figure 24The database model for CRS system is shown below. The M-N of the Reservation has been converted properly to a table as seen in the CRS database model in the following Figure.
Figure 25The notation used in this model is a Crows Foot. You can change to other notation from the Display > Notation menu as shown below.
Figure 26INSERTING SAMPLE REAL DATAFinally we insert sample real data into those tables using the MySQL scripts and DBDesigner 4 as done before. We have to take note on the order of the sample data insertion into the table so that not to violate the foreign keys constraints.Student Table MySQL Insert ScriptINSERT INTO Student VALUES('86778','saad@uum.edu.my','Saad bin ali','male','1970-10-23','Malay','012-3452321','123, Kolej Maybank, UUM','1234&$');INSERT INTO Student VALUES('89001','maidah@uum.edu.my','Maidah bt osman','female','1980-02-10','Malay','013-6753412','214, Kolej Guthrie, UUM','5%$#@1');INSERT INTO Student VALUES('85789','salmi@uum.edu.my','Salmi bt khalid','female','1976-07-12','Malay','016-6542378','201, Kolej Proton, UUM','yu65#$');INSERT INTO Student VALUES('84521','fong_32@yahoo.com','Fong Ah Kui','female','1978-09-05','Chinese','019-3823419','321, Kolej Maybank, UUM','h8$&j#');INSERT INTO Student VALUES('87991','santini@gmail.com','Santini a/p Kalupa','female','1960-01-21','Indian','016-7682314','133, Kolej Guthrie, UUM','k6y&$*');INSERT INTO Student VALUES('89667','salman@yahoo.com','Salman b. Ghani Khan','male','1969-05-04','Malay','013-6752312','103, Kolej Proton, UUM','rt5$&8');INSERT INTO Student VALUES('87669','fakhrul@uum.edu.my','Fakhrul ibni Sayuti','male','1972-01-17','Other','017-3416745','121, Kolej Maybank, UUM','7u5r%@');
Figure 27After the insertion we do verification by viewing all the inserted data for the table by running the SELECT statement.
Figure 28Payment Table MySQL Insert ScriptINSERT INTO Payment VALUES('5422240032001745','Saad bin ali','VISA','03/2009', 'Maybank','86778', 40.00);INSERT INTO Payment VALUES('3400220043007896','Maidah bt osman','Master', '10/2010','CIMB Bank','89001', 100.00);INSERT INTO Payment VALUES('2433340022005674','Salmi bt khalid','VISA','12/2009', 'Public Bank','85789',40.00);INSERT INTO Payment VALUES('3400240033223452','Fong Ah Kui','Master','07/2010', 'AM Bank','84521',70.00);INSERT INTO Payment VALUES('5600340022331233','Santini a/p Kalupa','VISA', '02/2010','RHB Bank','87991',70.00);INSERT INTO Payment VALUES('4500230034002314','Salman b. Ghani Khan','Master', '05/2012','Bank Islam','89667',40.00);CourtTimeTable Table MySQL Insert ScriptINSERT INTO CourtTimeTable(reserveDate,timeStart,timeEnd,ccNumber) VALUES('2007-08-05','08:00','10:00','5422240032001745');INSERT INTO CourtTimeTable(reserveDate,timeStart,timeEnd,ccNumber) VALUES('2007-08-07','10:00','12:00','3400220043007896');INSERT INTO CourtTimeTable(reserveDate,timeStart,timeEnd,ccNumber) VALUES('2007-08-05','16:00','18:00','2433340022005674');INSERT INTO CourtTimeTable(reserveDate,timeStart,timeEnd,ccNumber) VALUES('2007-08-08','18:00','20:00','3400240033223452');INSERT INTO CourtTimeTable(reserveDate,timeStart,timeEnd,ccNumber) VALUES('2007-08-08','20:00','22:00','5600340022331233');INSERT INTO CourtTimeTable(reserveDate,timeStart,timeEnd,ccNumber) VALUES('2007-08-09','18:00','20:00','4500230034002314');Court Table MySQL Insert ScriptINSERT INTO Court(courtType, courtNo) VALUES ('Badminton','A');INSERT INTO Court(courtType, courtNo) VALUES ('Tennis','A');INSERT INTO Court(courtType, courtNo) VALUES ('Badminton','C');INSERT INTO Court(courtType, courtNo) VALUES ('Sepak takraw','C');INSERT INTO Court(courtType, courtNo) VALUES ('Tennis','B');INSERT INTO Court(courtType, courtNo) VALUES ('Squash','B');INSERT INTO Court(courtType, courtNo) VALUES ('Ping pong','C');Reservation Table MySQL Insert ScriptINSERT INTO Reservation VALUES (1,1,'Available');INSERT INTO Reservation VALUES (2,2,'Booked');INSERT INTO Reservation VALUES (3,3,'Booked');INSERT INTO Reservation VALUES (4,4,'Booked');INSERT INTO Reservation VALUES (5,5,'Booked');INSERT INTO Reservation VALUES (6,6,'Available');Equipment Table MySQL Insert ScriptINSERT INTO Equipment(equipType, equipQty, courtID) VALUES ('ping pong bat',2,1);INSERT INTO Equipment(equipType, equipQty, courtID) VALUES ('badminton rackets',4,2);INSERT INTO Equipment(equipType, equipQty, courtID) VALUES ('tennis rackets',4,3);INSERT INTO Equipment(equipType, equipQty, courtID) VALUES ('tennis ball',10,4);INSERT INTO Equipment(equipType, equipQty, courtID) VALUES ('takraw ball',4,5);INSERT INTO Equipment(equipType, equipQty, courtID) VALUES ('ping pong ball',10,6);VIEWING THE TABLES’ DATAWe can also view the inserted data through the Edit Table Data context menu as shown below.
Figure 29
Figure 30To switch to other table, select the drop down control shown in the following Figure.
Figure 31
Figure 32
Figure 33
Figure 34
Figure 35
Figure 36USING SQL DML AGAINST THE DATABASEAfter successfully inserting sample data, we do some queries using DBDesigner against those tables to check any consistencies, redundancies and the robustness.SELECT name, ccNumber, ccExpiryDate FROM Student, Payment GROUP BY name;
Figure 37SELECT courtType, courtNo, status FROM Court, Reservation GROUP BY courtType;
Figure 38SELECT name, gender FROM Student WHERE gender = 'male';
Figure 39SELECT name, gender FROM Student WHERE race = 'malay';
Figure 40SELECT ccName, amount FROM Payment WHERE amount <= 70;
Figure 41SELECT reserveDate, timeStart FROM CourtTimeTable WHERE timeStart != '10:00:00';
Figure 42INSERT INTO Student VALUES('88798','Chang@uum.edu.my','Chang Ah Soon','male','1977-06-13','Chinese','019-5453243','207, Kolej TM, UUM','76%$j#');
Figure 43UPDATE Student SET emailAddr = 'chang_soon@uum.edu.my' WHERE matricNo = '88798';
Figure 44SELECT equipType, equipQty FROM Equipment WHERE equipQty = 4;
Figure 45CONCLUSIONThe analysis of the web site from the database design aspects done in this project is based on what we have learned before. It can be considered a reverse engineering process, analyzing the finished product, a web site and then finding the flaws and inconsistencies. In this case, the important parts in the database design include:- Finding and listing the entity types, attributes and relationships.
- Building the Entity Relationship Diagram (ERD).
- Converting the ERD to Relational Model.
- Preparing the SQL scripts from the Relational Model (tables).
- Inserting sample data.
- Testing using the SQL Database Manipulation Language (DML) against the database.
- Applying normalization rules for no. 1, 2 and 3 as needed.
We are not using the normalization techniques intensively in this project because the mandatory rule is just 1NF and whenever the situations exist. In order to produce a very good design, optimized, performance wise and using minimum resources, the actual database design process must be iteratively done. We also found that at the beginning stage a lot of common senses must be taken into consideration such as during the ERD building. This must be tightly bound to the purpose of the database system that is going to be developed, the users that will use the system and the database maintenance.Finally we have very good opportunities in applying what we have learned in this subject from concepts, principles and implementations. Other than the theoretical topics we also gain a lot of experiences using software and tools such as NetBeans, DBDesigner, MySQL and Derby databases.REFERENCES- Database Systems: A Practical Approach To Design, Implementation And Management 4/e 2007, by Thomas Connolly and Carolyn Begg, Pearson Education.
- Database Design, Application, Development & Administration, 3/e 2007 by Michael V. Mannino, McGraw-Hill.
You may want to see an example and learn on the data collection and analysis during the Planning and Analyzing stages of Information System Development.
