Malaysia is going into the 21 st Century with ICT implementation as one of the cornerstones of its ongoing modernization. Today across the whole world, governments are discovering that ICT technologies are becoming an effective tool for better governance. USM UNIT SUKAN, would like to overhaul their service in that line of thought. An overview how the USM UNIT SUKAN facility Service present operations in going on and how it would be computerized with web-based electronic booking and electronic management is presented. It will also include all the necessary hardware and software technology needed for this upgrade process. Thus, the modernized management of the Sports Facility through the electronic booking computerization has a great impact in their function. We also took in consideration how this would enhance the departments operation and efficiency so that would give an overall new image for the department at USM. At present moment all the functions and operations of the Unit Sukan is done manually. Beginning from buying the coupons by students and staff from the security office, which is the initial process before a student or staff would be able to use the sport facilities available at USM until the booking of the facilities is done and verification of usage of the said facilities by the respective party. We also took in consideration all types of facilities available and also all the different categories of users allowed to use the facilities as each of them would be required to follow different procedures before been allowed to use them. The functional tasks of the USM Unit Sukan are divided into six main areas of divisions according to the management and locality need of the department and the users. We have the following region of functionality:

a) Single Smart-card system for payment Module

b) Online access to booking facility Module

c) Biometric verification of the booking at the sport facility Module

d) Wireless communication and access Module

e) Facility Security and Safety Module

Hints: Make your own relevant or needed assumptions in answering the following question based on the above case study scenario. You are encouraged to use the SQL server 2005 to prepare you answers. You can submit your codes and the database that you created which would have higher evaluation.

Question 1

Based on the above case study, prepare a complete set of tables that would be needed to support USM Unit Sukan to develop system that would enable the storage of all the information. Identify all the important attributes for all the tables that you suggest. Provide a suitable data type for each of attributes to reflect the usability. Develop a normalized database for all the tables and you should show an example of each of the 1 st, 2 nd and 3 rd normalization process involved for your tables. [25 Marks]

Question 2

(a) For each of the tables created in question 1, identify all the primary keys, foreign keys and also any composite keys that would be used to maintenance the database. [10 Marks]

(b) Write the SQL commands to be used to create each of the tables that you would included in the creation of the database for USM Unit Sukan. Assume you own constraints and data verifications. [15 Marks]

Question 3

Analyse the case study and identify the entire possible user requirement for USM Unit Sukans daily and monthly operations as described above. List them down as different relationship with possible type of cardinality. Draw an estimated complete ER diagram that would be able to represent USM Unit Sukans complete database. [25 Marks]

Question 4

(a) Insert some of the appropriate data for each of the tables that you had created in the database. Use the SQL commands to insert 5 different tuples for each of the tables. [10 Marks] (b) Create your own queries (15) that would be used by the USM Unit Sukans management team to help them in their daily activities of using the database. Use the SQL commands to write your queries and provide the answer from you sample data above. [15 Marks]