闪电代写 -代写CS作业_CS代写_Finance代写_Economic代写_Statistics代写_代码代做_IT代写_加急帮助

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

COMP508 DATABASE SYSTEM DESIGN

SEMESTER 2, 2022

Individual Assignment

1. Introduction to Assignment Requirements

This assignment is designed to provide students with practical exposure to the stages involved in database development, including the development of entity relationship modelling, database design and implementation.

This is an individual assignment. It provides an opportunity for students to apply to a larger case study the database concepts discussed in class and practised in exercises.

A separate document on Canvas: Assignments – ‘Case Study for Database Project’, describes the case study in detail. Please read the case study carefully. Students may ask for additional clarification of the case study on the discussion board on Canvas.

The details of the three parts of the assignment and the tasks are specified in Section 2. Section 3 describes the submission details and dates. The marking scheme is in Section 4.

Expectations

The assignment includes 3 parts. Part A consists of Tasks 1 and 2. Part B is comprised of Tasks 3 and 4. Part C has task 5. Students are required to attempt all the tasks.

Tools & Templates

For this assignment, you must use Oracle SQL Developer and the specified CASE tool, Visual Paradigm CE version.

Plagiarism means borrowing from the work of another without indicating by referencing that the ideas expressed are not one’s own.

Unauthorised Collaboration

Unauthorised collaboration means joint effort between students or students and others in preparing material submitted for assessment, except where this has been pre-approved by the paper programme. Students are encouraged to discuss matters covered in classes, but the expression of ideas and arguments must be the student’s work.

ACADEMIC INTEGRITY GUIDELINES

AUT takes Academic Integrity very seriously and you are reminded that the following actions may be deemed to constitute a breach of the General Academic Regulations Part 7: Academic Discipline, Section 2 Dishonesty During Assessment or Course of Study

• 2.1.1 copies from, or inappropriately communicates with another person

• 2.1.3 plagiarises the work of another person without indicating that the work

is not the student’s own – using the full work or partial work of another person without giving due credit to the original creator of that work

• 2.1.4 collaborates with others in the preparation of material, except where this has been approved as an assessment requirement.

• 2.1.5 resubmits previously submitted work without prior approval of the assessment board

• 2.1.6 using any other unfair means.

ADDITIONAL INFORMATION

• Your assessment responses must be your own work. You may be required to orally defend your responses to assessment questions.

Part A Entity Relationship Modelling and Logical Database Design (35 marks)

Task 1 Identify Entities & Develop Business Rules [10 marks]

Identify all entities and relationships in the case study and develop a set of business rules. You should follow the syntax given below. Two rules must describe each relationship, one in each direction.

Each/A/An ENTITY_1 May/Must Relationship_Verb_Phrase number ENTITY_2

Task 2 Construct a logical Entity Relationship Diagram (ERD) [25 marks]

Based on the business rules developed in Task 1, construct a logical ERD for the case study using the Visual Paradigm tool.

• Identify all attributes in each entity, including all primary and foreign keys (transform any composite and multivalued attributes according to the rules of the relational model).

• Name all relationships (i.e. you must use verb phrases on both sides of each relationship)

• Identify the Cardinality and Participation for each relationship.

• Resolve all many to many (M:N) relationships.

• Describe any assumptions you have made in a text note on the diagram.

Please note:

1) You don’t need to identify data types in your ERD.

2) You don’t need to answer task 2 questions separately. You identify them on the diagram.

Part B Database Implementation (35 marks)

In this part, you should develop a database based on your logical ERD developed in Part A. You must populate the tables with realistic sample data.

Task 3 Create tables [25 marks]

Create the tables in Oracle. Write SQL scripts defining each table. The table definitions should include

• All attributes with appropriate data types

• All appropriate constraints such as primary key, foreign keys, and check statements (CHECK constraints).

• All constraints must be given names. Naming standards must be used.

Task 4 Populate data [10 marks]

Insert a small sample of realistic test data (5 – 10 rows minimum) into each table. All test data must be meaningful to demonstrate your understanding of the data.

After creating all your tables (Task 3) and inserting data (Task 4), run the SQL SELECT statement below:

SELECT table_name FROM user_tables;

The statement will retrieve a list of all tables that exist in your Oracle account (i.e., all tables you

created). Include the results of the SELECT statement in your assignment.

You must also include the following for each table in your database:

• SQL table creation script

• SQL insert script (used for inserting sample data)

• Sample data - you must run “SELECT * FROM table_name” (where table_name is the name of the table) for each of your tables in Task 3 and include results of SELECT statements for each table.

Part C Construct SQL Queries (30 marks)

Task 5 Construct SQL Queries [30 marks]

Identify five data retrieval requirements that would be used by the business described in the case study. For each requirement you have identified, construct a SQL query needed to display the data. So, you must construct five SQL queries in total. The queries must include a combination of the following SQL features :

• Use of restriction (WHERE clause), Arithmetic expressions, Concatenation of columns

• Use of comparison and logical operators

• Multiple tables join (e.g., Equijoin, Outer Join, and Self-join)

• Group functions (e.g., COUNT, SUM, AVG, MAX, MIN) and clauses (e.g., ORDER BY, GROUP BY, HAVING)

Note:

1. You may combine the preceding features in a single query. The design of each query should demonstrate your knowledge and application of SQL content covered in the course and understanding of the correct usage of the various features and clauses of SQL. A simple ‘select * from table_name’ will fetch you a mark of zero.

2. There should be no more than one query that involves a single table.

3. An example of a query is given on the next page.

For your reference, an example of a query from an Employee database is given below.

• Purpose of the query:

To search for staff with a specific qualification (e.g. BSc) and retrieve the following details (Staff Number, Staff Name (Concatenation of Last and First names) and their Qualification.