The Database Design - Report Example

SQL CREATE TABLE ments CREATE TABLE InstrID INT UNSIGNED NOT NULL AUTO_INCREMENT, F VARCHAR (15) NOT NULL, L VARCHAR (15) NOT NULL, Email VARCHAR (50) NOT NULL, Phone VARCHAR (15) NOT NULL, PRIMARY KEY (InstrID) ); CREATE TABLE Member( MemberID INT UNSIGNED NOT NULL AUTO_INCREMENT, FName VARCHAR (25) NOT NULL, LName VARCHAR (25) NOT NULL, Email VARCHAR (50) NOT NULL, Phone VARCHAR (15) NOT NULL, PRIMARY KEY (MemberID) ); CREATE TABLE PoolUsage( UsageID INT UNSIGNED NOT NULL AUTO_INCREMENT , UsageDateTime DATETIME NOT NULL , UsedBy ENUM(Member,Non Member) NOT NULL , PRIMARY KEY ( UsageID ) ); CREATE TABLE PoolMemberUsage( RecordID INT UNSIGNED NOT NULL AUTO_INCREMENT, UsageID INT UNSIGNED NOT NULL, MemberID INT UNSIGNED NOT NULL , PRIMARY KEY ( RecordID ), FOREIGN KEY (UsageID) REFERENCES PoolUsage(UsageID), FOREIGN KEY (MemberID) REFERENCES Member(MemberID) ); CREATE TABLE GymUsage( UsageID INT UNSIGNED NOT NULL AUTO_INCREMENT , UsageDateTime DATETIME NOT NULL , UsedBy ENUM(Member,Non Member) NOT NULL, PRIMARY KEY ( UsageID ) ); CREATE TABLE GymMemberUsage( RecordID INT UNSIGNED NOT NULL AUTO_INCREMENT, UsageID INT UNSIGNED NOT NULL, MemberID INT UNSIGNED NOT NULL , PRIMARY KEY ( RecordID ), FOREIGN KEY (UsageID) REFERENCES GymUsage(UsageID), FOREIGN KEY (MemberID) REFERENCES Member(MemberID) ); CREATE TABLE CourtUsage( BookingID INT UNSIGNED NOT NULL AUTO_INCREMENT, BookingDate DATE NOT NULL, BookingTimeFrom TIME NOT NULL, BookingTimeTo TIME NOT NULL, IssueDateTime DATETIME NOT NULL, RequestType (Member,Non Member) NOT NULL, PRIMARY KEY (BookingID) ); CREATE TABLE MemberCourtUsage( RecordID INT UNSIGNED NOT NULL AUTO_INCREMENT, BookingID INT UNSIGNED NOT NULL, MemberID INT UNSIGNED NOT NULL, PRIMARY KEY (RecordID), FOREIGN KEY (BookingID) REFERENCES CourtUsage(BookingID), FOREIGN KEY (MemberID) REFERENCES Member(MemberID) ); CREATE TABLE NonMemberCourtUsage( RecordID INT UNSIGNED NOT NULL AUTO_INCREMENT, BookingID INT UNSIGNED NOT NULL, NonMemberFName VARCHAR(15) NOT NULL, NonMemberLName VARCHAR(15) NOT NULL, PRIMARY KEY (RecordID), FOREIGN KEY (BookingID) REFERENCES CourtUsage(BookingID) ); CREATE TABLE ExerciseClass( ClassID INT UNSIGNED NOT NULL AUTO_INCREMENT, ClassDate DATE NOT NULL, ClassTimeFrom TIME NOT NULL, ClassTimeTo TIME NOT NULL, InstrID INT UNSIGNED NOT NULL, PRIMARY KEY (ClassID), FOREIGN KEY (InstrID) REFERENCES Instructor(InstrID) ); CREATE TABLE MemberClassRegistration( RegID INT UNSIGNED NOT NULL AUTO_INCREMENT, Feedback Enum(Likes,No Feedback), MemberID INT UNSIGNED NOT NULL, ClassID INT UNSIGNED NOT NULL, PRIMARY KEY (RegID), FOREIGN KEY (MemberID) REFERENCES Member(MemberID), FOREIGN KEY (ClassID) REFERENCES ExerciseClass(ClassID) ); CREATE TABLE NonMemberClassRegistration( RegID INT UNSIGNED NOT NULL AUTO_INCREMENT, Feedback Enum(Likes,No Feedback), NonMemberFName VARCHAR(15) NOT NULL, NonMemberLName VARCHAR(15) NOT NULL, ClassID INT UNSIGNED NOT NULL, PRIMARY KEY (RegID), FOREIGN KEY (ClassID) REFERENCES ExerciseClass(ClassID) ); Database Constraints The database design comprised of a number of contrainst to ensure that the database worked correctly. Referential constraints As the contraints of references i.e. the foreign key relationships were defined, the data entered in tables was accoding to the standard format, correct and in relevance to the previous records entered in the related tables. This ensures a consistent data model, leaving no room for incorrect data entry. For instance, for the pool usage by members, only the set of member id’s that are in the system are made available to the user to enter against. No member id can be entered which may not belong to a member. Entity Constraints In order to maintain a compact database structure and also ensuring the type of data that can be provided for the attributes in a record, the design was configured such that a certain type of data be entered into the attributes of integers, dates, time and characters. The attributes in the entities that are integer are defined as unsigned to ensure that only positive numbers are saved. The sizes and lengths of the characters was defined according to need rather than the available size. This reduced the space of the overall database increasing its speed as well. Secondly it also ensures that reasonable data is entered. Primary keys are defined for each entity to ensure that the uniquely identifies a record in the table entity. The primary keys are set to increment automatically for every new record entry. This ensures that a uniques value is always entered. In order to ensure that a particular value which should not be left empty, the particular attribute is defined to have NOT NULL value. In this case, a user cannot enter a record without providing the required data. In order to ensure that only a certain range of values can be entered for a field, ENUM was used to specify the values. Column data constraints In order to ensure that a certain type of data can be entered in a table field Primary keys with the feature of auto incrementing their value on every insertion has been defined. This way no two values in that particular field of the table can be the same. The fields that cannot be left empty are specified to be NOT NULL. This forces the record to have all the required data. ENUM is used to specify the list of values for a field to choose from. This ensures that no irrelevant information is provided. POPULATED TABLES Instructor Table Member Table PoolUsageTable PoolMemberUsage Table GymUsage Table GymMemberUsage Table CourtUsage Table MemberCourtUsage Table NonMemberCourtUsage Table ExerciseClass Table MemberClassRegistration Table NonMemberClassRegistration Table SQL Queries Query transaction (a) – a report listing pool usage over last two months. SELECT * FROM poolusage WHERE month( UsageDateTime ) >10 AND year( UsageDateTime ) =2012; Query transaction (b) – a report listing Gym usage over the last two months with information on whether the use was by members or non-members. SELECT * FROM gymusage WHERE month( UsageDateTime ) >10 AND year( UsageDateTime ) =2012 ORDER BY UsedBy; Query Transaction (c) – display how busy the pool is over a typical week. SELECT * FROM `poolusage` WHERE day(UsageDateTime)>0 AND day(UsageDateTime) Read More