Database Management Assignment Help
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.
|COST (NORMAL CLIENT)||INT||11|
|COST (SENIOR CLIENT)||INT||11|
|COST PER HOUR||INT||11|
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.
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.
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.
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.
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.
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.
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 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:
- 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.
- 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.
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.
www.tutorialspoint.com. (2016).ER Model Basic Concepts. [online] Available at: http://www.tutorialspoint.com/dbms/er_model_basic_concepts.htm [Accessed 25 Apr. 2016].
Techterms.com. (2016).Data Type Definition. [online] Available at: http://techterms.com/definition/datatype [Accessed 25 Apr. 2016]. Order Now