Reference no: EM132311208

Database Concepts and Programming Fundamentals -

Database Concepts Assignment -  

This is an individual assignment. There are five questions.

Question 1 - SQL

In addition to the lecture notes, you should also study by yourself the SQL*Plus tutorial on Canvas (the Oracle section) and other resources for syntax and useful functions.

The relational schema for the Academics database is as follows:

DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode)

ACADEMIC(acnum, deptnum*, famname, givename, initials, title)

PAPER(panum, title)

AUTHOR(panum*, acnum*)

FIELD(fieldnum, id, title)

INTEREST(fieldnum*, acnum*, descrip)

Some notes on the Academics database:

• An academic department belongs to one institution (instname) and often has many academics. An academic only works for one department.
• Research papers (PAPER) are often authored by several academics, and of course an academic often writes several papers (AUTHOR).
• A research field (FIELD) often attracts many academics and an academic can have interest in several research fields (INTEREST).

Primary keys are underlined and foreign keys are marked with *. You should download the SQL script for defining and populating the database academics.sql from Canvas (the Oracle section) and run academics.sql in your Oracle account to build the database.

Write ONE SQL query for each of questions 1.1) through to 1.10). Your query must run in Oracle SQL Developer.

 1.1. For each academic, give the acnum, givename, famname and the total number of papers s/he has written. Note that if an academic has not written any paper, his/her total should be zero. You can use or not use JOIN operators.

1.2. List departments where at least one academic does not have any research interest. List the deptnum, deptname and instname of these departments. You must use a subquery.

1.3. List the fieldnum, title and the total number of interested academics (under the heading "NO. ACADEMICS INTERESTED") for each research field that some academics are interested in. The list should be in increasing order of fieldnum. Note: research fields that no academics are interested in are excluded.

1.4. Find research fields that have at least ten interested academics. Give the fieldnum, title and the number of interested academics for these research fields.

1.5. Return the acnum of academic(s) who wrote the largest number of papers. You must NOT use MAX. An SQL query that lists all academics in decreasing order of their total number of papers is incorrect.

1.6. Give the total number of academics who have not written any papers. You must use the NOT IN operator.

1.7. Find research fields whose title contains the string 'data' and has at least one interested academic from the department with deptnum 100. List the fieldnum and title of these research fields. You must use the EXISTS operator. Ensure your query is case-insensitive.

1.8. List papers (panum) that have authors from the same department. Do NOT use any JOIN operator. Do NOT use any subqueries.

1.9. The SQL query below is meant to list research fields (fieldnum) where no academics from 'RMIT CS' (deptnum=126) have an interest. But it is incorrect. Give the correct SQL query.

select fieldnum

from interest, academic

where interest.acnum=academic.acnum and deptnum != 126;.

1.10. Consider the SQL query given below. Give the English explanation for the output of a) the subquery, and b) the whole SQL query. Literal explanation will receive zero marks.

select S.acnum

from interest S

where upper(S.descrip) like '%LOGIC%'

and exists (select fieldnum

from interest

where acnum = S.acnum

and upper(descrip) not like '%LOGIC%');

Question 2 - The Relational model

Consider the Customer Order database of the ABC company, which keeps data for customers and their orders. A database of only one relation is designed, as shown below: ABC(customerNo, customerName, phone, streetAddr, city, state, postCode, orderNo, productNo, quantity, unitPrice, salesRep)

• A customer has a unique customer number (customerNo) and is also described by name, phone number, street address (streeAddr), city, state, and postCode.
• An order is identified by a unique order number (orderNo).
• A product has a unique product number (productNo) and has a unit price (unitPrice).
• A customer can place several orders and an order is made by only one customer.
• An order can include several products and each has some quantity, and a product can be on several orders.
• An order has one sales representative (salesRep) and one sales representative can be responsible for several orders.

Answer questions:

2.1. Give all likely FDs.

2.2. Give the candidate keys for the ABC relation. Explain your answer.

2.3. Give {CustomerNo}+ and {orderNo, salesRep}+ based on the FDs for Question 2.1).

2.4. Is the relation ABC in BCNF or 3NF? Explain your answer.

Question 3 - Normalisation

A shop keeps data about customers, salesmen, products and orders. Consider the attributes below and their associated FDs, where empID is the employee ID for salesmen.

custNo → custName, address, credit-limit, discount

productNo → price, desc

orderNo → empID, custNo, discount

orderNo, productNo → quantity, empID

custNo, orderNo → discount

orderNo → address

Consider the below Transaction relation;

Transaction (custNo, empID, productNo, orderNo, quantity, discount)

Answer questions.

3.1. Give the minimal basis for the given FDs.

3.2. The Transaction relation is not in BCNF or 3NF. Give the reason using the FDs on attributes.

3.3. Follow the 3NF decomposition algorithm to decompose the Transaction relation into relations in BCNF or 3NF. Give;

• The relations after decomposition.
• Specify the primary key for each relation.
• Specify any foreign keys for each relation.

Question 4 - ER model

Due to your experience designing the database for the O Athletic Games, you are asked to design the ER diagram for a bigger database for the 2020 Summer Olympic games in Tokyo. Requirements are as follows.

Some sports organise events into disciplines (for ease of scheduling events). For example, the Hockey sport has two events "Men 12-team tournament" and "Women 12-team tournament". But the Aquatics sport has disciplines such as Swimming and Diving, and Swimming has events like "Men 100m freestyle" and "Women 100m butterfly".

• Each athlete competing at the Olympics has a unique athlete number and has a first name, last name, and other personal details. Each athlete must represent a country.
• Each Olympic venue has name and address and description of facilities. Each venue is designed for some sports or disciplines.
• Data should be kept for event schedules. Events are scheduled to venues with date and time according to its design purpose. An event has many participants and they first compete in groups numbered sequentially and then some are selected to participate in the final competition event for the medals.
• Event results for all athletes, including medals, rank and result recorded should be kept in the database. As a fictitious example, for Women's 50 metres freestyle swimming event, Cate Campbell won the Gold medal in first place with a recorded time of 24.42 seconds; Amanda Smith is placed fourth with a recorded time of 25.50 seconds.

You shall design the database such that it can be used to

• Search for Olympians and their results.
• Search the event schedule for event details.
• List sports and their events.

You are encouraged to use the "Olympian Search Page" of the Australian Olympic Committee as a motivating example for the usage of your database. The difference is that your database is only for the 2020 Tokyo Olympics and does not keep historical data for previous games.

According to the requirements, give the ER diagram for the database using the UML class symbols (as used in the lecture notes), making assumptions where necessary. You must represent entities, relationships and their attributes, and all applicable constraints in your diagram. Explain any constraints that cannot be expressed in the diagram.

Question 5 - ER to relational schema mapping (5 points).

Consider the below Metro Train database ER model using the UML Class symbols.

• The Employee-Driver-TicketInspector-StationMaster subclass hierarchy is partial and disjoint.
• A train line can have several runs numbered sequentially and it can be express.
• Some stations are premium stations with facilities such as toilets.

Map the diagram to a relational database schema. Indicate the primary key (underline) and any foreign keys (asterisk) in each relation.

Programming Fundamentals Assignment -

This is an individual assignment. You must demonstrate your assignment in your timetabled practical class prior to your final submission.

Objective - The main objective of this assignment is to familiarize you with object oriented design and programming. Object oriented programming helps to solve complex problems by coming up with a number of domain classes and associations. However identifying meaningful classes and interactions requires a fair amount of design experience. Such experience cannot be gained by classroom-based teaching alone but must be gained through project experience.

This assignment is designed to introduce different concepts such as inheritance, abstract classes, method overloading, method overriding, and polymorphism.

General Requirements -

1. Your final code submission should be clean, neat, and well-formatted (e.g., consistent indentations) and abide by the formatting guidelines.

2. Identifiers should be named properly and camel case e.g. UsedCar (class) and carPrice (variable). [Google "camel case"]

3. You must include adequate meaningful code-level comments in your program.

4. For each input from the user, display appropriate prompt message.

5. For each invalid input from the user, display appropriate error message.

6. IMPORTANT: your code should be able to compile and run under command-line.

Assignment overview -

A PlayStore is a standalone digital marketplace that allows users to browse and download mobile applications (APPs). The PlayStore also serves as a digital store offering publications like e-books and digital magazines. Applications and publication items in the PlayStore are either free or can be bought for a price.

The program you create will allow the creation of a store, filling it with products, creating users and simulating their interaction with the store (purchasing products, adding comments etc).

Assessment tasks -

Your program should consist of multiple class files where you can demonstrate your knowledge of inheritance, polymorphism, method overriding, abstract classes, etc. You need to write classes, add methods and variables to complete the following tasks performed by the admin of the PlayStore.

There are two sample/starter classes (PlayStoreMain.java and PlayStore.java) provided.

Section 1: The classes and their attributes

Group A - content classes

You may need to define methods wherever appropriate to support these classes.

Class Content - Mobile apps and publication items are Content of the PlayStore. Each Content (either application or publication) is associated with the following information: an ID, name, number of downloads, price, and reviews. Reviews is a collection of Comment objects (see Group B for details). Class Content cannot and should not be instantiated.

Class Application - Application is a type of Content. In addition to the data that a content class have, an Application object has an OS type that presents the minimum operating system requirement. An Application object can be initialized as Application g1 = new Application("g1", "Pokemon", 5.3, "androidV4");

In the above example 5.3 is the price of the app in dollars, "androidV4" is the OS requirement. Initially the number of downloads is zero, and the reviews are empty. Application app1 = new Application("app1", "Calendar", "androidV3");

If no price is provided, the application is then free.

Class Publication - Another type of Content is Publication. In addition to the data that the Content class has, a Publication object also has: publisher and number of pages.

Class Book - One type of Publication is Book, which has an additional data: the author name. Notes, it is possible that one book have multiple authors.

Group B - associated classes

Again, you may need to define methods wherever appropriate to support these classes.

Section 2: Functionalities of the classes

User functionalities

1. Method becomePremium. A user can become a Premium user for a cost of $100. A premium user gets 20% discount on each purchase of the contents after becoming premium.

2. Method buyContent, where the parameter is a Content type of object. When a user buys any content, the price of that content needs to be deducted from the balance of that user. Do necessary checks before the deduction. You need to consider whether the user is a premium user or not in this step. The number of downloads of the content should increase after the purchase.

a. Exceptions must be thrown when trying to buy a content with an insufficient balance. The exception thrown must indicate the cause of error, show an appropriate error message, allowing the caller to respond appropriately and recover if possible. Note that when you add exceptions the method calls will need to be surrounded by try/catch blocks.

b. A user may buy multiple content. Write a method showContentBought in the User class to show the list of names of all the contents that the user has bought. You may add additional attributes in the User class if necessary.

Content and Comment functionalities

3. Write a method for the Content class, where a comment/review (which is a Comment type of object) from users can be added to a Content object.

4. Write a method showComments in the Content class to show all the comments of a Content object (e.g. a particular game or book).

PlayStore and Admin functionalities

5. Write a method showContent of the PlayStore class to show a list of all available contents.

Also write a method for each type of contents to show the list of contents of that type (e.g., show all applications, show all books, show all magazines).

Do you need to write a method for each type? Is that possible to use one method for this task? (Hint: You may find Java getClass() method in java.lang.Object useful).

Use of Java Collections

6. You are encouraged to use collections such as ArrayList and HashMap. ArrayList implements an array which can grow indefinitely. HashMap allows an association to be created between keys and objects. Using such classes also reduces the amount of code required as they provide methods for retrieving required objects easily.

Input and output -

Your program should hard code a list of objects including content objects, user objects and comment objects etc. for testing purpose. See the skeleton sample code. (During marking, we may replace these objects with our own to test your program).

You program should have a simple menu to allow an admin to perform aforementioned tasks such as:

• upgrading a member to premium account;
• purchasing one item for one user;
• listing all available contents;
• showing all purchased items of a user;
• showing all comments of a content;

Input validation and error handling should be implemented. Input and output should be inside of the class PlayStoreMain.

Attachment:- Assignment Files.rar