Database Management Assignment Help
Introduction
The major objective of this assignment is to provide the database designer a good experience in analyzing and designing a database for a specified problem. It will help the designer to understand the nature and purpose of database analysis and design.
Entity types and their attributes with primary and foreign key
The database of MFC (Magic Fitness Center) contains various tables and attributes. All the tables and their attributes including primary and secondary key used in the table are shown in the following table.
Relationship schema
| CLIENT TABLE | |||
| Attributes | Data type | Length | Description |
| CLIENT_ID | INT | 11 | PRIMARY KEY
AUTO_INCREMENT
|
| FNAME | VARCHAR | 45 | |
| LNAME | VARCHAR | 45 | |
| VARCHAR | 45 | ||
| STREET_NO | INT | 11 | |
| STREET_NAME | VARCHAR | 45 | |
| SUBRUB | VARCHAR | 45 | |
| POSTCODE | INT | 11 | |
| CITY | VARCHAR | 45 | |
| STATE | VARCHAR | 45 | |
| GENDER | VARCHAR | 45 | |
| DOB | DATE | ||
| CLASS_TYPE | VARCHAR | 45 | |
| RFNAME | VARCHAR | 45 | |
| RLNAME | VARCHAR | 45 | |
| RPHONE | INT | 11 | |
| RSTREET_NO | INT | 11 | |
| RSTREET_NAME | VARCHAR | 45 | |
| RSUBRUB | VARCHAR | 45 | |
| RPOSTCODE | INT | 11 | |
| RCITY | VARCHAR | 45 | |
| RSTATE | VARCHAR | 45 | |
| INSTRUCTOR TABLE | |||
| Attributes | Data type | Length | Description |
| INS_ID | INT | 11 | PRIMARY KEY
AUTO_INCREMENT
|
| FNAME | VARCHAR | 45 | |
| LNAME | VARCHAR | 45 | |
| VARCHAR | 45 | ||
| STREET_NO | INT | 11 | |
| STREET_NAME | VARCHAR | 45 | |
| SUBRUB | VARCHAR | 45 | |
| POSTCODE | INT | 11 | |
| CITY | VARCHAR | 45 | |
| STATE | VARCHAR | 45 | |
| GENDER | VARCHAR | 45 | |
| DOB | DATE | ||
| CLASS_TYPE | VARCHAR | 45 | |
| POSITION | VARCHAR | 45 | |
| BRANCH TABLE | |||
| Attributes | Data type | Length | Description |
| BRANCH_ID | INT | 11 | PRIMARY KEY
AUTO_INCREMENT
|
| BRANCH_NAME | VARCHAR | 45 | |
| BRANCH_ADDRESS | VARCHAR | 45 | |
| PHONE_NO | INT | 11 | |
| FAX_NO | INT | 11 | |
| STAFF TABLE | |||
| Attributes | Data type | length | Description |
| STAFF_ID | INT | 11 | PRIMARY KEY
AUTO_INCREMENT
|
| STAFF_FNAME | VARCHAR | 45 | |
| STAFF_LNAME | VARCHAR | 45 | |
| POSITION | VARCHAR | 45 | |
| GENDER | VARCHAR | 45 | |
| DOB | DATE | ||
| SECTION | VARCHAR | 45 | |
| BRANCH_ID | INT | 11 | FOREIGN KEY |
| MEMBERSHIP TABLE | |||
| Attributes | Data type | Length | Description |
| MEMBERSHIP_ID | INT | 11 | PRIMARY KEY
AUTO_INCREMENT
|
| MEMBERSHIP_TYPE | VARCHAR | 45 | |
| COST (NORMAL CLIENT) | INT | 11 | |
| COST (SENIOR CLIENT) | INT | 11 | |
| CLASS_TYPE TABLE | |||
| Attributes | Data type | Length | Description |
| CLASS_ID | INT | 11 | PRIMARY KEY
AUTO_INCREMENT
|
| CLASS_NAME | VARCHAR | 45 | |
| COST PER HOUR | INT | 11 | |
| GYM_CLASS TABLE | |||
| Attributes | Data type | Length | Description |
| GYMCLASS_ID | INT | 11 | PRIMARY KEY
AUTO_INCREMENT
|
| CLASS_ID | INT | 11 | FOREIGN KEY |
| DATE | DATE | ||
| TIME | TIME | ||
| CLIENT_ID | INT | 11 | FOREIGN KEY |
| CFNAME | VARCHAR | 45 | |
| CLNAME | VARCHAR | 45 | |
| MEMBERSHIP_ID | INT | 11 | FOREIGN KEY |
| CASUAL_CLIENT | VARCHAR | 45 | |
| CLASSS_TIME TABLE | |||
| Attributes | Data type | Length | Description |
| CLASS_TT_ID | INT | 11 | PRIMARY KEY
AUTO_INCREMENT
|
| INS_ID | INT | 11 | FOREIGN KEY |
| DATE | DATE | ||
| TIME | TIME | ||
| DAY | VARCHAR | 45 | |
| CLASS_ID | INT | 11 | FOREIGN KEY |
| FOODS TABLE | |||
| Attributes | Data type | Length | Description |
| FOOD_ID | INT | 11 | PRIMARY KEY
AUTO_INCREMENT
|
| FOOD_NAME | VARCHAR | 45 | |
| PRICE | INT | 11 | |
| SEMINAR_CUSTOMER TABLE | |||
| Attribute | Data type | Length | Description |
| CUST_ID | INT | 11 | PRIMARY KEY
AUTO_INCREMENT
|
| CUST_FNAME | VARCHAR | 45 | |
| CUST_LNAME | VARCHAR | 45 | |
| START_DATE | DATE | ||
| END_DATE | DATE | ||
| ROOMS TABLE | |||
| Attribute | Data type | Length | Description |
| ROOM_NO | INT | 11 | PRIMARY KEY
AUTO_INCREMENT
|
| ROOM_SIZE | INT | 11 | |
| ROOM_LOCATION | VARCHAR | 45 | |
| RENTAL_PRICE_PER_DAY | INT | 11 | |
| SEMINAR DETAIL | |||
| Attribute | Data type | Length | Description |
| DETAIL_ID | INT | 11 | PRIMARY KEY
AUTO_INCREMENT
|
| CUST_ID | INT | 11 | FOREIGN KEY |
| ROOM_NO | INT | 11 | FOREIGN KEY |
| NO_OF_GUEST | INT | 11 | |
| FOOD_ID | INT | 11 | FOREIGN KEY |
| EQUIPMENT TABLE | |||
| Attribute | Data type | Length | Description |
| EQUIPMENT_ID | INT | 11 | PRIMARY KEY
AUTO_INCREMENT
|
| EQUIPMENT_NAME | VARCHAR | 45 | |
| SUPPLIER_ID | INT | 11 | FOREIGN KEY |
| FAULTE_DATE | DATE | ||
| FAULT_TIME | TIME | ||
| COST | INT | 11 | |
| PURCHASE DATE | DATE | ||
| SUPPLIER TABLE | |||
| Attribute | Data type | Length | Description |
| SUPPLIER_ID | INT | 11 | PRIMARY KEY
AUTO_INCREMENT
|
| SUPPLIER_NAME | VARCHAR | 45 | |
| ADDRESS | VARCHAR | 45 | |
| PHONE_NO | INT | 11 | |
| EQUIPMENT_ID | INT | 11 | FOREIGN KEY |
In this assignment we have designed database to record and store the full details of every client and gym instructor of Magic fitness center. First we categorized several tables for database and their attributes then we draw a logical diagram of the database to show the relationship between different table by using primary key and foreign key.
We have to consider several things while designing database to make it a good one and worth to be implemented.
Attribute
An attribute is a type of element which is associated with an object and takes a value, object like an item, a page, a region etc. In other words we can say that an attribute is the characteristics. In terms of database management system an attribute is a component of database like table, field. It mainly describes the instance in the row of a database.For example in the clients table the client_id, client_name, client_address etc. is the attributes of clients table.Entity
An entity is the piece of data of an object or concept stored in the database. It can be a real world object, either living or non-living which can be identifiable easily. All the entity has their attributes which give them their identity or define them. In the designed table client, instructor, class, foods etc. are the entity.
Datatype
Data type is the storage format of data in which they are stored in the database. The data type of any data contains a specific range and type of values. Some common example of data types are integer, float, date, variable-character (varchar), strings and many more. Every column in the database table is required a specific name and data types.
Primary key
In database management system, a primary key is a column in the table which uniquely identifies or defines the rows of table.The values putted in the primary key column must be unique for each row of the table. In database management of information system only one primary key allowed in each table so no duplication is allowed and the primary can’t be a null value or cannot accept a null value. In the designed database of Magic fitness center client_id, Ins_id, staff_id, class_id etc. are primary key in the client table, instructor table, staff table and class table respectively.
Foreign key
In the database management system, foreign key is the column which points to primary key. It cannot create index automatically so database designer have to manually create the index.The foreign key can accept multiple null values and it may have more than one in a table.In the designed database of Magic fitness center Branch_id, supplier_id, cust_id, room_no, food_id etc. are the foreign key in the staff table, equipment table andseminar_detail table respectively.
The another things we have to consider while making the logical diagram of database and designing a database is the normalization and types of normalization of database.
Normalization
In the database management system, normalization is the process, by which we properly organize the data in the database to reduce the redundancy and different types of anomalies like insertion anomaly, update anomaly, and deletion anomaly in the database.
Anomaly
Anomaly is the type of error which occurs when database is not normalized properly. It is mainly of three types:
- Insertion anomaly:The insertion anomaly is the incapability to add the data to the database due to the absence of another data.
- Update anomaly:The update anomaly is exists when one or more instances of duplicated data are updated, but not all.
- Deletion anomaly:The deletion anomaly is the accidental loss of stored data due to deletion of other data.
Types of normalization
- First normal form (1NF):In the first normal form of normalization each column is unique. It is defined in the definitions of relations between tables itself. According to first normal form of normalization all the attributes in the relation must have the atomic domains. The values in an atomic domain are indivisible units.
- Second normal form (2NF):A table is consider to be in second normal form, if it contains both the condition given below:
- Table must be in first normal form
- No any non-prime attribute is dependent on the proper subset of any candidate key of table
- Third normal form (3NF):A table is considered in third normal form, if it follow both the condition given below:
- Table must be in second normal form
- Transitive functional dependency of non-prime attribute on any super key should be removed
Entity Relationship Diagram
Entity Relationship Diagram is a pictorial representation of the relationship between the dataof the tables. It describes the manner, in which entities of two or more tables are related to each other. Here we have made 14 tables and they are:
CLIENT TABLE
- INSTRUCTOR TABLE
- BRANCH TABLE
- STAFF TABLE
- MEMBERSHIP TABLE
- CLASS_TYPE TABLE
- GYM_CLASS TABLE
- CLASSS_TIME TABLE
- FOODS TABLE
- SEMINAR_CUSTOMER TABLE
- ROOMS TABLE
- SEMINAR DETAIL TABLE
- EQUIPMENT TABLE
- SUPPLIER TABLE
Note:The attributes marked with yellow diamond are primary keys of respective tables and the red marked diamond shows the foreign key of that particular table. The cardinality (one to one, one to many, many to many) has shown through crow’s foot notation to make it more readable and understandable.
The below ER diagram has been made using MySQL Workbench which is a standard tool for making databases and its diagrams. The concept of normalization has been kept in mind and the diagram is in third normalized as required by the case study.
Assumptions
- All the tables have their primary key that uniquely identifies that table.
- Wherever required we have break down the name and address to make it multivalued attribute.
- Client table has one to many relationships with Gym_class table.
- Class_type has one to many relationships with gym_class.
- Class_type has one to many relationships with class_time.
- Membership has one to many relationships with Gym_class.
- Instructor has one to many relationships with class_time.
- Branch has one to manyrelationships with staff.
- Equipment has one to many relationships with supplier.
- Food, Rooms, Seminar_customer has one to many relationships with Seminar_detail table.
Conclusion
This assignment is really helpful to understand the fundamental concepts of database designing such as tables, data types,entities, attributes, several keys, ER diagram and so on.
References
www.tutorialspoint.com. (2016).ER Model Basic Concepts. [online] Available at: //www.tutorialspoint.com/dbms/er_model_basic_concepts.htm [Accessed 25 Apr. 2016].
Techterms.com. (2016).Data Type Definition. [online] Available at: //techterms.com/definition/datatype [Accessed 25 Apr. 2016]. Order Now
No comments:
Post a Comment