Sports Club Entity Model - Essay Example

Sports Club Entity Model Task 1: The various entities and their associations are decided on the basis of the case study description and based on some assumptions as follows: 1. Bookings are done by members for multiple courts 2. A member is bound to have a membership type which makes it 1:1 cardinality 3. A member has a relationship of 1:1 with subscription as they are bound to pay for continuation of their membership. 4. A member is to be enrolled with a coach. There are some members with no coaches. The cardinality among the various entities is a result of various assumptions made in practice. The entity relationship diagram focuses on the participation of entities and associations among them. Entity Relationship Diagram: Sports Club  This report contains a complete description of the Sports Club entity model. The report is organized into two distinct sections detailing entities and relationships.  Model Description: Model Entities: Assigned Booking Coach Court Member Membership_Type Subscription Model Relationships: gets_assigned for have_types makes makes_subscription to_a_coach Entity Details Entity Name: Assigned Relationships: gets_assigend, to_a_coach Attributes: Name Key Not null Unique Description Coach_ID_assigned yes yes yes   Coach Id assigned Member_ID_assigned yes yes yes   Member Id assigned Entity Name: Booking Relationships: for, makes Attributes: Name Key Not null Unique Description Booking_ID yes yes yes   Unique Identifier for all Bookings Booking_Member_ID no yes no   Member Id for all Bookings Booking_Court_ID no yes no   Court ID for all Bookings Booking_Date no yes no  Booking Date Entity Name: Coach Relationships: assigned Attributes: Name Key Not null Unique Description Coach_ID yes yes yes    Unique Identifier for all Coach Coach_Name no no no   Name of the Coach Coach_Address no no no   Address information of Coach Coach_Contact no no no   Contact information of Coach Coach_Type no no no   Type of Coach Coach_Qualifications no no no   Qualifications of Coach Coach_Awards no no no   Awards won by the Coach Entity Name: Court Relationships: for Attributes: Name Key Not null Unique Description Court_ID yes yes yes  Unique Identifier for all Court Court_Type no no no   Type of Court (squash or tennis) Court_Surface_type no no no   Type of Surface (tennis) Court_Location no no no   Location of Court Court_Comments no no no   Comments regarding the Court Entity Name: Member Relationships: assigned, have_types, makes, makes_subscription Attributes: Name Key Not null Unique Description Member_ID yes yes yes  Unique Identifier for all Member Member_name no no no   Names of Member Member_Address no no no   Addresses of Member Phone no no no   Contact numbers of Member Membership_Type_ID no yes no   Membership type of Member Entity Name: Membership_Type Relationships: have_types Attributes: Name Key Not null Unique Description Membership_Type_Uni_ID yes yes yes  Unique Identifier for all Membership ID Membership_Type_Name no no no   Types (full, student, OAP, off-peak, junior, racquets, fitness) Entity Name: Subscription Relationships: makes_subscription Attributes: Name Key Not null Unique Description Sub_ID yes yes yes  Unique Identifier for all Subscription Payment_Amount no no no   Payment amount of the Subscription Payment_Date no no no   Payment date for the Subscription Payment_Type no no no   Payment type of Subscription Due_Date no no no   Payment due-date Subscription_Member_ID no yes no Member Id paying the subscription   Relationships Relationship Name: for Source End [is_done_for] : Booking (many,mandatory) Destination End [has_been_booked] : Court (one,mandatory) Relationship Name: gets_assigend Source End [ ] : Member (one,mandatory) Destination End [ ] : Assigned (many,mandatory) Relationship Name: have_types Source End [has_type] : Member (one,mandatory) Destination End [belongs_to] : Membership_Type (one,mandatory) Relationship Name: makes Source End [books] : Member (one,mandatory) Destination End [is_booked_by] : Booking (many,mandatory) Relationship Name: makes_subscription Source End [pays] : Member (one,mandatory) Destination End [is_received_from] : Subscription (one,mandatory) Relationship Name: to_a_coach Source End [ ] : Assigned (many,mandatory) Destination End [ ] : Coach (one,mandatory) The referential integrity has been enforced using the help of various cardinality relationships among the entities. Task : 2 SQL scripts: CREATE TABLE Membership_Type( Membership_Type_Uni_ID INTEGER NOT NULL, Membership_Type_Name INTEGER, -- Specify FK as unique to maintain 1:1 relationship -- Specify the PRIMARY KEY constraint for table "Membership_Type". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_Membership_Type PRIMARY KEY (Membership_Type_Uni_ID) ); CREATE TABLE Coach( Coach_ID INTEGER NOT NULL, Coach_Name VARCHAR(100), Coach_Address VARCHAR(200), Coach_Contact VARCHAR(100), Coach_Type VARCHAR(100), Coach_Qualifications VARCHAR(100), Coach_Awards VARCHAR(100), -- Specify the PRIMARY KEY constraint for table "Coach". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_Coach PRIMARY KEY (Coach_ID) ); CREATE TABLE Court( Court_ID INTEGER NOT NULL, Court_Type VARCHAR(100), Court_Surface_type VARCHAR(100), Court_Location VARCHAR(100), Court_Comments VARCHAR(200), -- Specify the PRIMARY KEY constraint for table "Court". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_Court PRIMARY KEY (Court_ID) ); CREATE TABLE Member( Member_ID INTEGER NOT NULL, Member_name VARCHAR(20), Member_Address VARCHAR(100), Phone NUMERIC(8,2), Membership_Type_ID INTEGER, -- Specify the PRIMARY KEY constraint for table "Member". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_Member PRIMARY KEY (Member_ID) ); CREATE TABLE Subscription( Sub_ID INTEGER NOT NULL, Payment_Amount INTEGER, Payment_Date DATE, Payment_Type VARCHAR(100), Due_Date DATE, Subscription_Member_ID INTEGER NOT NULL, -- Specify FK as unique to maintain 1:1 relationship -- Specify the PRIMARY KEY constraint for table "Subscription". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_Subscription PRIMARY KEY (Sub_ID,fk1_Member_ID) ); CREATE TABLE Booking( Booking_ID INTEGER NOT NULL, Booking_Member_ID INTEGER, Booking_Court_ID INTEGER, Booking_Date DATE, -- Specify the PRIMARY KEY constraint for table "Booking". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_Booking PRIMARY KEY (Booking_ID,fk1_Member_ID) ); CREATE TABLE Assigned( Coach_ID_assigned INTEGER NOT NULL, Member_ID_assigned INTEGER NOT NULL, -- Specify the PRIMARY KEY constraint for table "Assigned". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_Assigned PRIMARY KEY (Coach_ID_assigned,Member_ID_assigned) ); ALTER TABLE Membership_Type ADD CONSTRAINT fk1_Membership_Type_to_Member FOREIGN KEY(fk1_Member_ID) REFERENCES Member(Member_ID); ALTER TABLE Subscription ADD CONSTRAINT fk1_Subscription_to_Member FOREIGN KEY(fk1_Member_ID) REFERENCES Member(Member_ID); ALTER TABLE Booking ADD CONSTRAINT fk1_Booking_to_Member FOREIGN KEY(fk1_Member_ID) REFERENCES Member(Member_ID); ALTER TABLE Booking ADD CONSTRAINT fk2_Booking_to_Court FOREIGN KEY(fk2_Court_ID) REFERENCES Court(Court_ID); ALTER TABLE Assigned ADD CONSTRAINT fk1_Assigned_to_Member FOREIGN KEY(fk1_Member_ID) REFERENCES Member(Member_ID); ALTER TABLE Assigned ADD CONSTRAINT fk2_Assigned_to_Coach FOREIGN KEY(fk2_Coach_ID) REFERENCES Coach(Coach_ID); Data Dictionary: Figure 1: Table 1 Figure 2: Table 2 Figure 3: Table 3 Figure 4: Table 4 Figure 5: Table 5 Figure 6: Table 6 Figure 7: Table 7 Reports: Figure 8: Report on Member List Task : 3 Navathe (2004) defines database as a collection of programs that enables users to create and maintain a database. It can also be defined as general purpose software for defining, creating, fetching and sharing databases among various users. The flavors of the databases used for e-commerce websites follow a different pattern set for storing data and offer various storage patterns and features to enable it to be distinguished from the competitor. Database technology would make sure that all the data and information would be captured and retrieved accordingly. The various functionalities would include scalability of various operations, ease of performing updations and modifications of data, maintaining the integrity of data, security of data is ensured, efficient recovery manager, maintains concurrency control and plan for recovery techniques and many more. All this would promote the functioning of the airline reservation system. Choosing a database model is of greater importance. Relational data model is based on relational data structures, integrity constraints and smooth access to Data Definition Language and Data Manipulation Language statements for creation and retrieval of data. It is based on relational algebra (Navathe, 2004). The database for family budgeting system would include features like atomic values, primary and foreign key relationships, and normalization process to reduce redundancy and anomalies of insertion, modification and deletion errors, row and column structure of the database tables and all kind of relationships are possible, one-to-one, one-to-many, many-to-one and many-to- many. The differences between Relational, Hierarchical and Network databases: Data warehousing is a concept that is used for storing organization’s data and is usually termed as corporate memory. It contains the raw material for an enterprise’s MIS or DSS system. The analyst can perform complex queries which would be used for getting results and further interpretation of the data and the resultant information (William, 2000). The subject oriented feature of the data warehousing takes into account the various elements that take place in the real world. It is non-volatile and integrated with respect to the data that they are never deleted and contains all the information with regard to business processing by the enterprise for all its operations. Data mining in contrast is filtering the data for the purpose of deriving a knowledge from it which is not possible for getting the trends of data from simple databases. It uses complex technologies for getting the better meaning of the data and its analysis. It is generally used for mainly two purposes namely knowledge discovery and prediction that roughly means that future prediction of events and patterns are found for getting the knowledge out of it for business intelligence purposes (Frawley, 2001). Online Analytical Processing (OLAP) is a concept which imbibes the very notion of decision making. OLAP is all about decision making by analyzing the large chunk of information which is termed as operational data. It primarily models data into a database style of representation namely the star schema which shapes the operational data into a multidimensional pattern to ease decision making. Figure 9: OLAP Concept and Distribution The various meanings of OLAP can be described as follows: Figure 10: Different Meanings of OLAP The various thinking of the OLAP is in the differentiation strategy followed to break up the concept into OLAP thinking, languages, product layers and fully optimized OLAP products. These various concepts would enhance the definition and its segregation swiftly. OLAP concepts give a notion of the exact business scenario and the real world. It constitutes the very idea for visualizing the objects in real world whether it is tangible or non-tangible. It simply focuses on the multi-dimensional concepts. OLAP product layers take its place on the top of database layers which is responsible for querying the entire database and fetching the result set. It is solely responsible for interacting with the database. The above demarcations lay the foundation that OLAP carry several meanings which are not ambiguous but often overlap in concepts to generate a clear meaning. The very purpose is to design a system taking into account the OLAP facilities and correlate the very meaning of modeling operational data with multidimensional data patterns. The different meanings strengthen the very principal of OLAP concept and brings about the inclusion of several penetrating factors for coming up with a holistic system for handling the operational data, preparation of SQL query patterns, identification of product layers and compilers and language constructs which enforce decision making. The web concept of including OLAP features finds a place in the OLAP concepts and various meanings propagate the access methodologies which enhances the decision making at various access points (Stemple, 2006). The web layered architecture’s penetration in the different meanings of OLAP gives it a right shape to the data transfer and acknowledges the data patterns for fetching information (Agarwal, 2007). Task: 4 The assignment was a great opportunity to expose self to various challenges in the database scenario and to enrich the theoretical knowledge into practice. The database concepts regarding the integrity and referential concepts have been well versed in this assignment. Primarily the entities and their roles and responsibilities have been fetched. The associations are viewed and thus mapped into the entity relationship model. The various cardinality among the entities in the ERD is well taken care using the help of the business scope and its various constraints. The SQL query and reporting tools of Oracle 10 G were used extensively to depict the final relations into physical tables. The database concepts and advanced concepts were researched evenly from journals and internet to understand deeply their usage and the various technical and business aspects of the technology. The primary concepts were fetched and revised in accordance to the various illustrations depicted in the case study. The integrity and referential integrity constraints are identified in accordance to the various business challenges faced in the real world. References/Bibliography Agarwal, Rakesh, Ashish Gupta, and Sunita Sarawagi (2007). “Modeling Multidimensional Databases.” Proceedings of the Thirteenth International Conference on Data Engineering. Birmingham, U.K, April 7—11. Frawley W.(2001). "Knowledge Discovery in Databases: An Overview". AI Magazine: pp. 213-228. ISSN 0738-4602. Navathe, Elmasri (2003). Fundamentals of Database systems, Singapore: Pearson Education. Stemple, David, Adolfo Socorro, and Tim Sheard (2006). “Formalizing Objects Using ADABPTL.” Advances in Object-Oriented Database Systems, 2nd International Workshop. William H. Inmon (2000). Using the Data Warehouse, John Wiley & Son's, ISBN 0-471-05966-8. Read More