Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

1. Tasks: The idea behind this practical assignment is give you an opportunity to demonstrate and showcase what you have learnt about data modelling, relational database design and SQL. To do so, you...

1 answer below »
1. Tasks: The idea behind this practical assignment is give you an opportunity to demonstrate and showcase what you have learnt about data modelling, relational database design and SQL. To do so, you are asked to build a database application for a real-world scenario – which is allocated to you by your tutor. You will design a conceptual as well as a relational schema for the database, and you will create an actual database using a relational database management system (Oracle or another DBMS of your choice – but NOT Access!). You will populate the database with sample data and write interactive queries and modifications on the database. More specifically, the tasks you will need to perform for this assignment are as follows: o Your first step is to describe the requirements for your assigned scenario. Your requirements should include data requirements and a sample of transaction requirements. You can see an example on how to express data application requirements in the appendix (see end of this document). o Construct an entity-relationship diagram for the data (see Chapter 4). When expressed in the entity-relationship model, you might want your design to have in the range of five or so entity sets/classes, and a similar number of relationships. Be aware, however, that certain entity sets/classes or relationships could sometimes be represented by attributes. You should certainly try to include different kinds of relationships (e.g., many-one, many-many) and different kinds of data (strings, integers, etc.), but your application is not required to use advanced features, such as sub-classing, multi-way relationships, or weak entity sets, if they are not appropriate for your application. o Use the method for translating an E/R diagram to relations described in the textbook (Chapter 5) to produce a set of relations from your E/R design. Make sure you specify explicitly the various keys (primary and foreign) in your relational schema. Are there any flaws in the relational database schema you get from this translation process? Are there opportunities to combine relations without introducing redundancy? If so, indicate which, and if not, tell us there are none. Are there examples of non-BCNF relation schemas? If so, do you want to decompose them? Is there anything you still don't like about the schema (e.g., attribute names, relation structure, duplicated information, etc.)? If so, modify the relational schema to something you prefer. o Write an SQL database schema for your scenario, using the CREATE TABLE commands. Pick suitable data types for each attribute. Page 119 of the textbook gives you the principal options regarding types (remember Oracle likes you to use VARCHAR2 for character strings, so please use this type). o Choose and declare primary keys and (if appropriate) other unique attributes or sets of attributes. Similarly, declare the foreign keys within your schema. o Execute INSERT commands to insert tuples/records into one of your relations (this data is chosen randomly by you). Show the response of sqlplus and the relation that results when you issue a SELECT * command. o Your database almost certainly includes relations that are expected to join with each other. For example, you may have a Student relation with attribute courseNo that's expected to join with attribute number in relation Course. In generating data, be sure to generate values that actually do join - otherwise all of your interesting queries will have empty results! One way to guarantee ‘joinability’ is to generate the values in one relation, then use the generated values in one relation to select joining values for the other relation. For example, you could generate course numbers first (either sequentially or randomly), then use these numbers to fill in the courseNo values in the student relation. o Write five queries on your database, using the SELECT construct of SQL. To receive full credit, each of your queries should exhibit some interesting feature of SQL: subtotals, sub-queries, or queries over more than one relation, for example. o Write three data modification commands on your database. These commands should include updating one or more record(s) and deleting one or/more record(s). o Create two views on top of your database schema. Show your CREATE VIEW statements and the response of the system. Also, show a query involving each view and the system response. Finally, show a script of what happens when you try to modify your view, say by inserting a new tuple into it. Is either of your views updatable? Tell why or why not? (essentially, a view is updatable if it is a selection on one base table.) o Add two attribute-based CHECK constraints to relations of your database schema. Show the revised schema, its successful declaration, and the response of the DBMS to inserts or updates that violate the constraints. o Conclude your work with a piece of reflection. Reflecting on the whole assignment does not mean that you just describe what happened whilst doing the assignment. You will also need to cover what you have learnt: what went well, what went wrong, how you overcame the challenges, what you would you do differently in the future, … etc. This is an important section of the report, so make sure you spend some time thinking about it and writing it. 2. Report: Your well-structured report should contain and describe all of the above tasks. All the SQL scripts should be included as well as a narrative around all the tasks. You will need to explain and describe what you are doing as you go along performing each task.
Answered Same Day May 08, 2020 Swinburne University of Technology

Solution

Shikha answered on May 12 2020
136 Votes
Database Systems        2
Sweet Shop – Database Systems
Submitted By
Course
Professo
Date
Application Requirements
1. Data Requirements
Employees – All the staff members are responsible for performing their daily activities. The data included in the employees table will be – EmployeeID, Employee_name, address, city, contact_no, job_profile, salary. Here, we will keep EmployeeID as a unique, so that they can be searched easily on the basis of their employeeid.
Supplier – Suppliers will be responsible for supplying all the ingredients that are required to make all the sweets. The main attributes of supplier will be SupplierID, Supplier_name, Supplier_address, Contact_person, Supplier_contact.
Products – This is the main table of the whole database as it will save all the details of all the sweets. This will be master table of sweets. All the inventory of the sweets will be saved in this. The main attributes of the table will be ProductID, Product_name, Stkinhand, price etc.
Customers – This table will be used to store all the important details of a customer. The main attributes of customer table will be CustomerID, customer_name, customer_address, customer_city, customer_contact. When customer comes in the shop, the staff will store all the customer details in the database.
Purchase – The sweet shop will purchase various sweets from various suppliers. After receiving an order from supplier, the quantities of items will be updated in items table as well. The main fields of purchase table will be PurchaseID, purchase_date, supplierid, purchase_amount, discount, employyeid.
Purchase_details – This will be used to save all the item details of a purchase order. The main attributes will be Id, PurchaseID, ProductID, qty, price.
Sale – Sale as the name suggest will be used to save all the details of a sale to a customer. The main attributes will be Sale_id, ProductID, CustomerID, qty, price, sale_date, employeeid.
2. Transactional Requirements
Data Entry
· The information of a new employee with employeeid=’E005’ will be inserted in the Employees table.
· A new supplier will be added to supplier table.
· Products received from supplier will be inserted into products table unique productid.
· When a customer a
ives to the sweet shop, all the details of customer will be saved in customer table.
· All the purchase from supplier will be done by creating purchase order with unique purchase order number.
· The items which are ordered on particular purchase order will be saved in purchase_details.
· The sales which are made to customer will be inserted in sales table.
Queries
· List all the details of product that has been sale with the details of a custome
· List all the details of Products that are ordered from a particular supplier.
· List the total value of sale which has been made to particular customer in a given month.
· List the employees who has not made any sale.
· List the details of the products that has not been ordered in a given month.
Entity Relationship Diagram
Relational Schema
1. Employees EmployeeID, Employee_name, address, city, contact_no, job_profile, salary
Primary key EmployeeID
2. Supplier SupplierID, Supplier_name, Supplier_address, Contact_person, Supplier_contact.
Primary key SupplierID
3. Products ProductID, Product_name, Stkinhand, price
Primary key ProductID
4. Customers CustomerID, customer_name, customer_address, customer_city, customer_contact
Primary key CustomerID
5. Purchase PurchaseID, purchase_date, supplierid, purchase_amount, discount, employeeid.
Primary key PurchaseID
Foreign key ...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here