TCS4424 Advanced Database

2024-10-07 Admin 写评论

Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due

TCS4424
 Advanced Database
 Faculty Header ID:
 Contribution: 50%
Course Leader:
Ts. Hazlina Haron
Coursework
Demo Date:
Arranged by lecturer
Deadline Date:
Week 10 (Nov 2024)

Plagiarism is presenting somebody else's work as your own. It includes: copying information directly from the Web or books without referencing the material; submitting joint coursework as an individual effort; copying another student's coursework; stealing coursework from another student and submitting it as your own work. Suspected plagiarism will be investigated and if found to have occurred will be dealt with according to the procedures set down by the University. Please see your student handbook for further details of what is/isn't plagiarism.

All material copied or amended from any source (e.g. internet, books) must be referenced correctly according to the reference style you are using.

Coursework Submission Requirements
  • An electronic copy of your work for this coursework must be fully uploaded on the deadline date shown above.
  • For this coursework you must submit a single PDF document. In general, any text in the document must not be an image (i.e. must not be scanned) and would normally be generated from other documents (e.g. MS Office using "Save As ... PDF").
  • Make sure that any files you upload are virus-free and not protected by a password or corrupted otherwise they will be treated as null submissions.
  • Please ensure that all screen shots are readable. Pixelated images that cannot be read easily will be disregarded and their contents will score zero.
  • You must NOT submit a paper copy of this coursework.
  • All coursework must be submitted as above. Under no circumstances can they be accepted by academic staff.

INTENDED COURSE LEARNING OUTCOMES

CLO1
Explain and apply the concepts and techniques of Database Management Systems`, Database Design Methodology and Database Administration to the development and operation of an information system
CLO2
Explain and critically evaluate key concepts relating to Distributed Databases, Data Mining, Data Warehousing and OLAP.
CLO3
Systematically create an Entity-Relationship (ER) Model from a given situation and map it to a normalised relational model using CASE tools.
CLO4
Design, implement, optimise and critically evaluate an information system using an enterprise DBMS

MAPPING

Assessment
 Weightage
 Intended Course Learning Outcomes
Examination
 50%
 CLO1, CLO2, CLO3, CLO4
Data Modelling
 40%
 CLO3
Queries
 10%
 CLO4

Briefing

This coursework should be completed as a pair (i.e. group of 2). If you need to work alone, please get approval from your lecturer. You must be ready to demonstrate your work as a pair to your peers on the dates specified by your course coordinator.

The aim of this assignment is to undertake a range of tasks involved in the analysis, design and build of a database structure and applications for a database system. Where information is not available you should make reasonable assumptions. Please ensure that you include all business constraints that have been captured during the analysis phase. The scenario below is intentionally vague. This is to allow you the opportunity to show initiative by making sensible assumptions or spotting opportunities to remove inconsistencies/errors in the scenario.

Specification

The Educational Resource Database

It has been noticed that different people learn in different ways and therefore prefer different materials when learning a new subject. However, teachers and lecturers produce materials for their students in the way that makes sense to them. That may not work for everyone, so you have decided to create a database which will allow students (and staff) to add records of any useful materials that they find.

A subject is made up of topics. A student should be able to search by subject, e.g. “relational database” and see what comes up. If they wish, they can then search that subject list by a topic.

This may be a website, a book, a television, a YouTube video, etc. Your database should also keep track of any costs involved, for example the price of a book or the cost of an on-line tutorial package.

A teacher or lecturer should also be able to search the database in the same way as a student, but also to produce well formatted reports on topics which they can distribute to their students.

Requirements

Create the conceptual ERD, physical ERD, database (with test data) and queries to support this educational resource.

Sample Queries

The following database queries are required to be displayed on screen (i.e. produce the SQL code only, no form, report or web page). Choose one of the sets of queries to answer. If you are in a pair, you cannot choose the same set of queries as your partner.

List A

A1. Joining tables
List all the titles of books and their authors.
A2 ORDER BY
List the topics for a subject ordered by the name of the topic.
A3 GROUP BY … HAVING
List the topics for each subject.
A4 Sub-queries
Create a query which shows the number of each type of material for each subject.

List B

B1. Joining tables
List all the on-line videos and their URLs.
B2 ORDER BY
List the authors of books ordered by the name of the author and the title of the books.
B3 GROUP BY … HAVING
List the web sites for each subject.
B4 Sub-queries

Create a query which shows the number of each type of material for each topic.

Deliverables

Your electronic submission should include:
  • You should upload a report which includes all the deliverables shown below (D1, D2, D3, D4, D5 and D6) with sufficient evidence that each step works.
All deliverables are listed below:

D1. One A4 page containing the conceptual data model diagram (i.e. an entity relationship diagram using consistently either Chen or Crows Foot notation showing:

  • Relevant entity types
  • The primary key for each entity (underlined)
  • Relationship types with role names (plus relationship attributes if any)
  • Any sub-classes (showing disjoint or overlapping) which would make the diagram more informative.
  • Structural constraints on each relationship (both cardinality and participation)

Note: If you show attributes other than the primary keys (e.g. foreign keys) on the conceptual model then you will be penalised.

D2. For the above model, produce the physical data model. The model represents your mapping from the conceptual model into a relational schema and should show:
  • All entities and their relationships to the other entities with their cardinalities.
  • Resolve any sub-classes and M-M relationships as necessary.
  • For each prospective table identify the primary key and any necessary foreign keys then add all appropriate fields.
  • Identify the data types for each field.
D3. Make a clear statement of any assumptions that you make about the data, in particular noting any information that you believe should be included but is not mentioned in the outline specification or any assumptions that affect the design of your database.

D4. Create a database for the above schema using the Oracle DBMS using SQL and populate each table with typical records to clearly demonstrate the application results. Create an insertion plan and then use an on-line data generation site to generate a suitable quantity of test data for each table. Give the SQL code for the creation scripts and samples of the population scripts. You should populate the database with sufficient data to clearly and unambiguously demonstrate all queries submitted.

D5. The SQL queries used to fulfil the four queries chosen from A1 to A4 or B1 to B4. Show the output results (i.e. screen dumps of your executed queries).

D6. Include a detailed evaluation of your work.

Submission Procedure

You are to separate D5 & D6 as an Individual Report. Include a proper cover page for Individual Report that display SEGi University logo, faculty details, student details, lecturer name and submission date.

Please submit 2 reports in PDF files: Individual Report and Group Report. Individual Report contains screenshot of SQL queries/output from Oracle Developer (D5) and work evaluation (D6). Group report contains D1 – D4.

Group work carries 40% and Individual work carries 10% of the coursework marks.

Marking Scheme

Conceptual Model
 Out Of
 Awarded
Reasonable assumptions made
 5
 3
Identifying necessary main entities
 5
 3
Identifying valid PKs
 2
 2
Identifying sub-classes and their type
 8
 0
Identifying relationships
 5
 5
Cardinality of relationships
 5
 5
Participation constraints of relationships
 5
 0
Sub-total
 35
 18

Physical Model
 Out Of
 Awarded
Appropriate list of attributes
 4
 3
Sub-classes and M-Ms resolved
 8
 4
The correct placement of FKs
 4
 4
Proof normalisation carried out
 4
 2
Sub-total
 20
 13

Coursework Implementation
 Out Of
 List A
 List B
Population plan and DB creation
 4
 3
Population implementation
 4
 3
Query 1 - Joins
 6
 5
Query 2 - ORDER BY
 8
 4
Query 3 - GROUP BY … HAVING
 8
 6
Query 4 - Sub-queries
 10
 5
Sub-total
 40
 26
 0


Out Of
 Awarded
Evaluation
 5
 3



TOTAL
 100
 60

Extra Materials

LucidChart (https://www.lucidchart.com/pages/usecase/education)

https://youtu.be/0fNdAD5zzNs The educational edition of LucidChart

https://youtu.be/mK__h9IDwVY Creating ERDs using LucidChart 

https://youtu.be/DjRkOVufTMk Creating a MySQL database using LucidChart

Object Oriented Relational Database Design

https://youtu.be/AnSusilaWBw An introduction to the topic.
https://youtu.be/kS8IUqZl6WU Stage 1 – Finding the objects.
https://youtu.be/MnZRqssGbms Stage 2 – Linking the objects.
https://youtu.be/L7cKjALS47o Stage 3 – Adding the basic cardinalities.
https://youtu.be/M8aA41fK1Rg Stage 4 – Adding the primary keys (PKs).
https://youtu.be/XgX-3nJmIwQ Stage 5 – Adding the foreign keys (FKs).
https://youtu.be/K_uk0TuDtk4 Stage 6 – Adding the fields (Yes, this IS the last step!).
https://youtu.be/qF1RNMVRVwA Changing the design – the easy bit.

https://youtu.be/dKQdjGKDRGQ Extending the cardinalities.

https://youtu.be/k_IaW7gVtf0 The types of ERDs; conceptual, logical and physical.
https://youtu.be/xSUXjS9xRkg An introduction to adding and resolving sub-classes in your ERDs.

SQL

https://youtu.be/aWrh0v0AYVI The world of databases
https://youtu.be/ra690idDLug Installing EasyPHP to use MySQL. (https://www.easyphp.org)
https://youtu.be/TiMh--puUkg Using SQL to create and manipulate tables definitions (DDL).
https://youtu.be/6W1eNpI1EPQ Using a data generator 1 – Planning.
https://youtu.be/x7I81fxHsHg Using a data generator 2 – Defining the tables.
https://youtu.be/mcJXEACNeyU Using a data generator 3 – Adding the data.
https://youtu.be/am_fg-eLiPc The basic uses of the SELECT command.
https://youtu.be/Tn6nN8_zupM Using the DML commands; INSERT, UPDATE and DELETE.
https://youtu.be/oIhdRkmD40Q Using wild cards in SELECT queries.
https://youtu.be/CR_9ewbOQuk Using BETWEEN and IN within SELECT queries.
https://youtu.be/u2ffbYeCKhI The uses of ORDER BY in SELECT queries.
https://youtu.be/9f03UwLYIWw How to link multiple tables in SQL.
https://youtu.be/CrXD7j46bgw Using function in SQL.
https://youtu.be/IoKoL2_1SVw Using variables and last_insert_id in SQL to PKs and FKs aligned.
https://youtu.be/vVVhxu06Fz8 Using GROUP BY and HAVING to analyse data using SQL.
https://youtu.be/MAsZxBXUm0Y Creating and using VIEWs
https://youtu.be/w4eRZcQzHSo Creating sub-queries and inner-joins
https://youtu.be/zJhBZ55hjKI Using self-joins to simplify some queries

DB Theory

https://youtu.be/Y7ulFqYjaT4 What are transactions? Video 1
https://youtu.be/PguCDI_fi3U What are transactions? Video 2
https://youtu.be/VxGKvqHhU5c An explanation of two-phase locking.
https://youtu.be/gDO3XMsgnl0 What is concurrency?
https://youtu.be/NHKHzwolbKU An overview of ACID.
https://youtu.be/cVQUvZh64Y8 An introduction to relational algebra.

发表评论

电子邮件地址不会被公开。 必填项已用*标注