<Deliverable 2 Waffle>
i) An introduction which describes the purpose of this deliverable.
This is the second deliverable of the COMP219-07A project.
It lists all of the changes our group has made to our original database design for Orion Games from project deliverable 1. Many of these changes were minor but we felt they simplified and improved our database considerably.
In this deliverable, we also described the rules and constraints for each table that we felt were necessary to ensure that correct data was entered into the database. We then wrote SQL statements that generated the database tables and added constraints to various attributes in the tables. The database is now ready for data to be inserted into it.
A paper prototype has been included that shows how we intend intend the C# application to look and function. This identifies all of the Visualstudio controls we plan to use, the events associated with these controls and what will happen when these events are triggered. This will greatly assist us when working on Project Deliverable 3.
To clarify how the group collaborated when working together on this deliverable, we have attached a summary of the minutes taken from the group's meetings.
ii) Any changes made to the database design from Part 1.
There were a couple of changes made to our database schema from Project Deliverable 1.
These are detailed below.
1) We decided to make all attributes representing a date be of the 'char(10)' data type rather than the 'date' data type.
2) We had only allowed a maximum of three characters for the gplatform attribute. This turned out to be too little as some platform names are greater than 3 characters in length. (eg: XBOX360). We extended the data type to a varchar(10).
3) All primary keys are now generated using IDENTITY(seed,increment) rather than the user having to specify a key value for each entity.
For the 'mid' primary key in the 'member' entity we use IDENTITY(990000,1)
For the 'gid' primary key in the 'games' entiy we use IDENTITY(1,1)
For the 'hid' primary key in the 'hire' entity use IDENTITY(1000,1)
4) We added two extra attributes to the 'member' entity.
mmobile char(11) which contains the members mobile phone number.
mjoindate char(10) which contains a string representing the date the member joined Orion games.
The attribute mphone was renamed to mhomephone.
5) We added an attribute to the 'member' entity called mnum_overdue which keeps a tally of how many games this member returns that are overdue.
iii) A textual overview for each table describing the rules and check conditions (described in English, not SQL code) that will be needed to enforce constraints. This must be done in the report document, not as comments in the SQL code.
Rules/Types:
Both the hire and member entities have attributes representing dates.
Therefore we decided that a separate 'dates' type was appropriate which will be a varchar(10).
A rule should be binded to this new type to ensure all dates follow the same format. (DD/MM/YYYY)
Member Table:
This table contains many details about each Orion Games member such as name and contact details.
All email addresses must have an '@' symbol contained within them so a check that there is an '@' symbol contained inside a value entered for the 'memail' attribute is required.
All addresses that are entered into the 'maddress' attribute should start with a number.
The format for 'mhomephone' should be the area code (07) followed by 7 numbers. A check will be made to ensure that entries follow this format.
The format for 'mmobile' should start with '02' followed by either a 1 or a 7 followed by a dash and then 7 numbers. A check will be made to make sure all entries follow this format.
Game Table:
This table contains many details about each game owned by Orion Games.
There is a limited number of different game genres so a check will be made to ensure an entry matches one of these. This will be done by checking if what the user entered is inside the set of game genres.
This is also the case for 'gplatform', 'gstatus' and 'gclas'. Each of these attributes have a limited set of possible values so we will check that a user entry matches an element in the particular attribute's set of values.
Hire Table:
This table contains the details of each hire made from Orion Games. A single hire includes multiple games. Its foreign key 'mid'references the 'member' table and tells us which member made each hire. There are no special restraints required as the attributes with 'dates' type are already covered by the binded rule above.
Includes table:
This table links hires and the games that are included in each hire.
The rating can only be 1 2 3 4 5 so we should add a check to make sure that the rating entered by the user is greater than zero and less than or equal to 5.
iv) All SQL statements used to create datatypes, rules and tables.
See above.
v) See pics. On Wednesday when we decide on the final design I will sketch this out.
vi)
THIS NEEDS TO BE DONE