It combines professional features and a clear and simple user interface to offer the most efficient way to handle databases. The database used is MySQL 5. The following list is the machine specifications used to install DBDesigner in this tutorial:. Intel Pentium Core 2 Duo, 2.
|Country:||Saint Kitts and Nevis|
|Published (Last):||27 August 2004|
|PDF File Size:||7.17 Mb|
|ePub File Size:||16.42 Mb|
|Price:||Free* [*Free Regsitration Required]|
Database 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. 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:.
SQL commands for creating the tables. You are advised to use the real data. SQL commands for viewing the related information for each screen. The results of task 4. The following screenshots are the web interfaces for CRS system. 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. If we restrict the reservation for the student only, then the occupation column can be dropped because all or majority should be a student. 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. 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. Saad bin ali. Maidah bt osman. Salmi bt khalid. Fong Ah Kui. Salman b. Ghani Khan. Fakhrul ibni Sayuti. 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.
Sarip Abdul bin Ghani. Saripah bt Malik. CIMB Bank. Hamzah bin Ismail. Public Bank. Irene Khoh Eng. AMM Bank. RHB Bank. Ong Ka Ting. Bank Islam. The 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 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. The 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. The following is a Court table and a sample data. This table is linked to Reservation and CourtTimeTable tables.
Sepak takraw. Ping pong. The 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. The 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. In 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.
The matric number and email address can be candidate keys. 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. The following are the relational model for the previously created ERD diagram. Next, we convert the relational model to MySQL scripts. Figure Then 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 8. Figure 9. To create a new table, select and drop or click the table icon shown below and click on the editor designer. Next, we can start designing the table model. Select and right-click the table.
dbdesigner 4 tutorial