Taifuniversity Fall 2021 College Of Computer & Information T

Taifuniversity Fall 2021 College of Computer & Information Technology Project 1 Database Implementation

The main goal of this project is to give students hands-on experience with using a Database Management System (DBMS) to create and manipulate a small database. Students should be able to create database systems, write SQL queries, interact with a particular DBMS, and manipulate the created database.

Students are to choose one of the suggested environments provided by the instructor or develop their own. They may work individually or in groups of up to three students. The selected environment should be used to develop a database system, including designing an Entity-Relationship (ER) diagram, defining the database schema, creating the database using SQL, and writing queries to manipulate and retrieve data.

Submission requirements to earn full credit include:

  • An ER diagram representing the database structure.
  • The database schema, detailing tables, attributes, and relationships.
  • The SQL creation script (.sql file) to build the database.
  • At least five SQL queries with their results, ensuring each query returns at least one row.
  • A sample of tested data, with a minimum of five rows in each relation or table.

Suggested environments for the project include:

  • Car Rental organization
  • Airport
  • English Language Institute
  • Amazon
  • Library
  • Restaurant

Paper For Above instruction

This project offers an invaluable opportunity for students to integrate theoretical knowledge of database design with practical skills in SQL and database management. By selecting an environment such as a library or a car rental organization, students can model real-world scenarios, creating a comprehensive database system that reflects the complexities and relationships inherent in the chosen environment.

Designing an ER diagram is the initial step, requiring students not only to identify entities such as customers, vehicles, or books but also to define the relationships among these entities. For example, in a library system, entities might include Book, Member, Loan, and Staff. Relationships may include "borrows" or "returns," with appropriate cardinalities such as one-to-many or many-to-many. Correctly designing this diagram is crucial, serving as the blueprint for the subsequent schema creation.

The schema definition involves translating the ER diagram into normalized tables and attributes, establishing primary and foreign keys to enforce referential integrity. Students must pay attention to data types, constraints, and indexes to optimize performance and ensure data consistency. An accurate and well-structured schema lays the foundation for effective database operations.

The SQL creation script (.sql) file is the practical implementation of the schema, containing commands to create tables, define constraints, and populate initial data. Populating each table with at least five sample rows allows for meaningful testing of queries and demonstrates understanding of data relationships.

Writing a minimum of five SQL queries enables students to demonstrate their grasp of data retrieval and manipulation. Queries might include complex joins, filtering, aggregation, or subqueries. For each query, students should provide the output, verifying that the query returns at least one row and illustrating practical use cases within the chosen environment.

For example, in a car rental system, one query could retrieve all available cars of a specific model, while another could calculate the total revenue generated in a particular month. These exercises reinforce querying skills and the ability to extract meaningful insights from the database.

Overall, this project encapsulates essential competencies in database design, SQL programming, and data management, equipping students with the skills needed for real-world applications in various industries.

References

  • Cameron, D. (2019). Fundamentals of Database Systems. Pearson.
  • Fundamentals of Database Systems (7th ed.). Addison-Wesley.
  • Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management. Cengage Learning.
    • Database Concepts. Pearson. Database System Concepts. McGraw-Hill Education.
  • Date, C. J. (2012). Database Design and Relational Theory: Normal Forms and All That Jazz. O'Reilly Media.
    • Modern Database Management. Pearson. Database Management Systems. McGraw-Hill.
  • Pratt, T., & Adamski, J. (2018). Visualizing Data: Exploring and Understanding Data Through Graphics. O'Reilly Media.

Related Assignments