Please read the following instructions carefully before solving & submitting the assignment:
It should be clear that your assignment will not get any credit if:
· The assignment is submitted after the due date.
· The submitted assignment does not open or file corrupt.
· The assignment is fully or partially copied (from a student, handouts or internet).
· The assignment is not submitted in .doc or .docx format.
Uploading instructionsYour submission must include:
Assignment submission through email is NOT acceptable.
The objectives of this assignment are to able the Students:
Allowed softwares for the solution:
Lectures Covered: This assignment covers Lecture # 25 - 30
Your assignment must be uploaded/submitted on or before 04/08/2020.
Our third assignment is the continuation of our second assignment. In the second assignment one question is related to the relational data model.
The relational data model is given below:
User (user_id, name, number, password, address, cart_id)
Medicine (med_id, name, price, expiry_date, shelve-id, cart_id, Order_id)
Order (order_id, medicines, total_price, time, date, user_id, pharm_id, payment_id)
Prescription (pres_id, image_url, Order_id)
Payment (pyment_id, card_number, amount, status, date, time)
Pharmacist (pharm_id, name, username, password, number, address)
Shelve (shelve_id, number)
CreditCard (card_number, expiry_date)
ShoppingCart (cart_id, cart_items, amount)
Now in third assignment, you are required to perform the following tasks:
1) Convert the above relational data model into a physical data model. For which you have to first create a database. The database name should be as your student id. For example, if your student id is BC123456789, then the database name should be BC123456789. [3 marks]
If the database name is different from your student id, then your assignment will be graded as zero.
2) After the database creation, you are required to write the SQL queries in order to create the above nine tables in the database. You are also required to apply different constraints like primary key constraint and foreign key constraints where applicable. [6 marks]
Marks will be deducted if primary and foreign key (where required) constraints are not implemented.
3) After the creation of tables, insert the following data in the given tables: [5 marks]
4) Write a query that retrieves the order_id, user_id, payment_id and date from the Orders table where total_price is equal or greater than 2000. [2 marks]
5) Write a query that display the Users table information as given below: [2 marks]
6) Write a query that add a column named as “Label” of varchar data type having 15 size in the “Shelve” table.
1. Only SQL Server and SQL Management Studio are allowed for the assignment solution.
2. Only SQL Queries are allowed to perform the given tasks. If a student use the wizard, then marks will be deducted.
3. You have to provide each SQL query and its execution snapshot of each task; otherwise your marks will be deducted.
First open the SQL Management Studio and connect to your SQL Server.
Then in the Object Explorer, right click and select the New Query in order to write different SQL Queries.
The SQL Query window will open and you can write different SQL queries in the highlighted area
For example, a select query is applied on the Payment table to retrieve all the records of the table, then:
For any query about the assignment, contact at CS403@vu.edu.pk