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
AttributesData typeLengthDescription
CLIENT_IDINT11PRIMARY  KEY

AUTO_INCREMENT

FNAMEVARCHAR45
LNAMEVARCHAR45
EMAILVARCHAR45
STREET_NOINT11
STREET_NAMEVARCHAR45
SUBRUBVARCHAR45
POSTCODEINT11
CITYVARCHAR45
STATEVARCHAR45
GENDERVARCHAR45
DOBDATE
CLASS_TYPEVARCHAR45
RFNAMEVARCHAR45
RLNAMEVARCHAR45
RPHONEINT11
RSTREET_NOINT11
RSTREET_NAMEVARCHAR45
RSUBRUBVARCHAR45
RPOSTCODEINT11
RCITYVARCHAR45
RSTATEVARCHAR45
INSTRUCTOR TABLE
AttributesData typeLengthDescription
INS_IDINT11PRIMARY  KEY

AUTO_INCREMENT

FNAMEVARCHAR45
LNAMEVARCHAR45
EMAILVARCHAR45
STREET_NOINT11
STREET_NAMEVARCHAR45
SUBRUBVARCHAR45
POSTCODEINT11
CITYVARCHAR45
STATEVARCHAR45
GENDERVARCHAR45
DOBDATE
CLASS_TYPEVARCHAR45
POSITIONVARCHAR45
BRANCH TABLE
AttributesData typeLengthDescription
BRANCH_IDINT11PRIMARY  KEY

AUTO_INCREMENT

BRANCH_NAMEVARCHAR45
BRANCH_ADDRESSVARCHAR45
PHONE_NOINT11
FAX_NOINT11
STAFF TABLE
AttributesData typelengthDescription
STAFF_IDINT11PRIMARY  KEY

AUTO_INCREMENT

STAFF_FNAMEVARCHAR45
STAFF_LNAMEVARCHAR45
POSITIONVARCHAR45
GENDERVARCHAR45
DOBDATE
SECTIONVARCHAR45
BRANCH_IDINT11FOREIGN KEY
MEMBERSHIP TABLE
AttributesData typeLengthDescription
MEMBERSHIP_IDINT11PRIMARY  KEY

AUTO_INCREMENT

MEMBERSHIP_TYPEVARCHAR45
COST (NORMAL CLIENT)INT11
COST (SENIOR CLIENT)INT11
CLASS_TYPE TABLE
AttributesData typeLengthDescription
CLASS_IDINT11PRIMARY  KEY

AUTO_INCREMENT

CLASS_NAMEVARCHAR45
COST PER HOURINT11
GYM_CLASS TABLE
AttributesData typeLengthDescription
GYMCLASS_IDINT11PRIMARY  KEY

AUTO_INCREMENT

CLASS_IDINT11FOREIGN KEY
DATEDATE
TIMETIME
CLIENT_IDINT11FOREIGN KEY
CFNAMEVARCHAR45
CLNAMEVARCHAR45
MEMBERSHIP_IDINT11FOREIGN KEY
CASUAL_CLIENTVARCHAR45
CLASSS_TIME TABLE
AttributesData typeLengthDescription
CLASS_TT_IDINT11PRIMARY  KEY

AUTO_INCREMENT

INS_IDINT11FOREIGN KEY
DATEDATE
TIMETIME
DAYVARCHAR45
CLASS_IDINT11FOREIGN KEY
FOODS TABLE
AttributesData typeLengthDescription
FOOD_IDINT11PRIMARY  KEY

AUTO_INCREMENT

FOOD_NAMEVARCHAR45
PRICEINT11
SEMINAR_CUSTOMER TABLE
AttributeData typeLengthDescription
CUST_IDINT11PRIMARY  KEY

AUTO_INCREMENT

CUST_FNAMEVARCHAR45
CUST_LNAMEVARCHAR45
START_DATEDATE
END_DATEDATE
ROOMS TABLE
AttributeData typeLengthDescription
ROOM_NOINT11PRIMARY  KEY

AUTO_INCREMENT

ROOM_SIZEINT11
ROOM_LOCATIONVARCHAR45
RENTAL_PRICE_PER_DAYINT11
SEMINAR DETAIL
AttributeData typeLengthDescription
DETAIL_IDINT11PRIMARY  KEY

AUTO_INCREMENT

CUST_IDINT11FOREIGN KEY
ROOM_NOINT11FOREIGN KEY
NO_OF_GUESTINT11
FOOD_IDINT11FOREIGN KEY
EQUIPMENT TABLE
AttributeData typeLengthDescription
EQUIPMENT_IDINT11PRIMARY  KEY

AUTO_INCREMENT

EQUIPMENT_NAMEVARCHAR45
SUPPLIER_IDINT11FOREIGN KEY
FAULTE_DATEDATE
FAULT_TIMETIME
COSTINT11
PURCHASE DATEDATE
SUPPLIER TABLE
AttributeData typeLengthDescription
SUPPLIER_IDINT11PRIMARY  KEY

AUTO_INCREMENT

SUPPLIER_NAMEVARCHAR45
ADDRESSVARCHAR45
PHONE_NOINT11
EQUIPMENT_IDINT11FOREIGN 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

information systemAn 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: 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

Maddox Smith

Hey,
Greetings for the day !
Hope that you’re well !

We want to introduce ourselves as a team of professionals who are into academic writing for the last 10+ years. We can provide assignment assistance in all subjects. Our experts can provide solutions across all the topics right from Management, HR, Marketing, Finance & Accounts, Statistics, IT, childcare, nursing, law, and general writing. We provide plagiarism free work and also send a ‘Turnitin’ report along with completed work. Our services are available at reasonable cost; we entertain amendment requests from clients without any extra charges.

Our Feature Included

Ø Every assignment includes graphical representation like pie chart, bar graph, smart art and all.
Ø Free 0% plagiarism report
Ø Expert team for technical work as well.
Ø On time delivery
Ø Multiple rework facility
Ø Huge team of expert in each subject
Ø Referencing like: Harvard, APA, MLA, Oscola, automatic referencing all are familiar to our experts.

Subject we cover: Math , finance, economics, accounts, civil engineering, mechanical engineering, IT, Computer science, electrical and electronics engineering, history, geography, political science, sociology, physiology, philosophy, biology, microbiology, biotechnology, biotechnology, B-school assignments, project report, psychology, nursing assignments, medical assignments, Tourists and travelling assignments all kinds of dissertation and so on

Best Regards:
Oz Paper Help
WhatsApp:+1 585-666-2225
Email:ozpaperhelp@gmail.com
www.ozpaperhelp.com
www.cheapassignmenthelp.co.uk
www.freeassignmenthelp.com

https://www.ozpaperhelp.com/
1 Step 1
GET INSTANT ASSIGNMENT HELP BY PHD EXPERTS FROM UNITED KINGDOM
keyboard_arrow_leftPrevious
Nextkeyboard_arrow_right