Creating Database For Melbourne Adventure Hub (MAH) Essay.

Database is a collection of information that are stored in structured way. It is used to store, maintain and access the data (Helskyaho, Yu, and Yu). In this assignment, we have created database for the Melbourne Adventure Hub (MAH). MAH is an organisation in Australia that run campaign on motel adventures in Australia. The organisation has several staff, activities, tour and customers. The report shows the database process while creating the database.

Initially, to create the database, it is recommended to draw an ERD diagram to get the blue print of it. The ERD diagram is an Entity relationship diagram (Amran, Mohamed and Bahry 2018). It acts as a blue print for the database. It includes entities and attributes and relationship among them. In the Melbourne Adventure Hub database, the entities identified as per the case study are:

Adventures, staff, Event_Calendar, Activities, Customer, Bookings, Payments, Brochure_Requests , adventure_activities , booking_activities.

Each entities have its own attributes. In addition, entities may have relationship with other entities.

The Attributes are defined with data types and constraints. Here, the constraints used were the Primary key and foreign key. Primary key are the column or combinations of column that are uniquely defined (Setyawati, Wijoyo and Soeharmoko, 2020). It contains any null values and the records must be unique. A table can have only one primary key.

Foreign keys are used to enforce referral integrity in the database (Wang and Yi 2020). Foreign key is the column in which the value of one table must match with the primary key of another table. In other words, it can also be said that the table containing foreign key is child table in reference to the table containing the primary key.

Relational schema is used to define the structure of the relation. It consist of relation name, set of attributes. The relational schema of Melbourne Adventure Hub (MAH) database is:

Adventures (Ad_Code, Duration, Price, Accom_Type, Description)

staff (Staff_Id, Name, Skills )

Event_Calendar (EC_Id, Ad_Code, Start_Date, Leader_id, Assistant1_id, Assistant2_id)

Activities(Ac_Code, Activities )

Customer (Custno, Name, Address )

Bookings (BookingId, Custno, Adventure , Ad_Code , Date, Fee )

Payments ( payment_Id, Custno, Ad_Code, Tour_Date, Payment_date, Amount_paid )

Brochure_Requests ( BR_ID, Date_Requested, Date_sent, Custno , Address, Ad_Code)

adventure_activities ( Ad_Code , Ac_Code )

booking_activities (BookingId , Ac_code )

For the Queries, SQL has been used. It is programming language used in relation database. It is divided into four categories. These are DDL , DML and DCL. The Data Definition Language command includes Create, Alter and Drop. The Data manipulation Language includes Select, insert, update and delete the records in table, And Data Control Language is sued to Grant and Revoke user privileges.

Conclusion

Thus, the assignment has been successfully. The table has been created and using data types, primary key and foreign key constraints. The database associated with  Melbourne Adventure Hub (MAH) consist of ten tables. All these table have been used to define the business organisation. It is necessary to know the concept of Primary key, foreign and key while designing the database. And SQL command has been used to query the database.

References

Amran, N., Mohamed, H. and Bahry, F.D.S., 2018. Developing human resource training management (HRTM) conceptual model using entity relationship diagram (ERD). International Journal of Academic Research in Business and Social Sciences, 8(12).

Helskyaho, H., Yu, J. and Yu, K., Machine Learning for Oracle Database Professionals.

Setyawati, E., Wijoyo, H. and Soeharmoko, N., 2020. Relational Database Management System (RDBMS).

Wang, Q. and Yi, K., 2020, June. Maintaining Acyclic Foreign-Key Joins under Updates. In Proceedings of the 2020 ACM SIGMOD International Conference on Management of Data (pp. 1225-1239).