Itech 1006 Database Management Systems Cricos Provider No 00 ✓ Solved

Itech 1006 Database Management Systemscricos Provider No 00103d It

For this assignment, you will implement and query a database from a supplied ER Diagram and Schema. You will be required to write the SQL statements to create the database structures, to fill the database with data, and to run queries on the data.

Sample Paper For Above instruction

The assignment involves translating a provided Entity-Relationship (ER) diagram into a relational schema, creating the corresponding SQL scripts for database creation, data insertion, and querying. The goal is to develop a functional relational database that accurately models the provided conceptual schema, populates it with sufficient data for testing, and demonstrates the ability to query and manipulate the data effectively.

First, the student must analyze the ER diagram and translate it into a relational schema using appropriate conventions, including correct table names, attribute names, data types, primary keys, and foreign keys. This schema should reflect all entities, relationships, and constraints detailed in the ER diagram. The schema is documented, including assumptions made during translation.

Next, the student will develop three core SQL files:

  1. Creating the database and tables: A script named nnnn_create.sql (with nnnn replaced by the student ID) to create a database named BHE_Holdings_nnnn, including all tables with their attributes, primary keys, and foreign keys correctly defined.
  2. Populating the tables with data: A script named nnnn_insert.sql with several INSERT statements to populate each table with representative and additional test data, including at least one record containing the student’s name with their student ID as the phone number.
  3. Writing queries for reports: A script named nnnn_query.sql comprising SQL SELECT statements to generate specific reports, such as listing customer details sorted by postcode, inventory items with supplier contact info, actors who are deceased, inventory by genre, and more. These queries should demonstrate SQL skills in data retrieval, filtering, sorting, updating, and deleting records.

The student must also prepare documentation including a cover page, the relational schema in diagram or tabular form, copies of the three SQL files, and APA references for all resources used. The final submission should be comprised of:

  • An MS Word or PDF documentation file with all required content.
  • The three SQL files: nnnn_create.sql, nnnn_insert.sql, and nnnn_query.sql, in .txt or .sql format.

Additional instructions specify the structure, content, and details of each SQL script, the inclusion of specific data for testing, and the types of queries required. The assignment emphasizes correct SQL syntax, proper use of relational design, adherence to database standards, and comprehensive testing.

Submission is via the designated Moodle assignment box, with emphasis on proper file formatting to enable command-line execution without errors.

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of database systems (6th ed.). Pearson.
  • Coronel, C., & Morris, S. (2016). Database systems: Design, implementation, & management (12th ed.). Cengage Learning.
  • Date: Date of publication or last update.
  • Rob, P., & Coronel, C. (2012). Database systems design, implementation, & management. Cengage Learning.
  • Date: Date of publication or last update.
  • Date: Date of publication or last update.
  • Author, A. (Year). Title of resource. Publisher. URL (if applicable)
  • Author, B. (Year). Title of resource. Publisher. URL (if applicable)
  • Author, C. (Year). Title of resource. Publisher. URL (if applicable)

Related Assignments