Latest Activity In Study Groups

Join Your Study Groups

VU Past Papers, MCQs and More

We non-commercial site working hard since 2009 to facilitate learning Read More. We can't keep up without your support. Donate.

Instructions

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 instructions

Your submission must include:

 

  • The assignment solution file which should be in .doc or .docx format.

 

Assignment submission through email is NOT acceptable.

Objectives

The objectives of this assignment are to able the Students:

 

  • To understand the physical design of database.
  • To implement the physical database by using the SQL Sever and SQL Management Studio.
  • To understand and execute different SQL queries.

 

 

Allowed softwares for the solution:

 

  • SQL Server and SQL Management Studio

 

 

Lectures Covered: This assignment covers Lecture # 25 - 30

 

 

Deadline

Your assignment must be uploaded/submitted on or before 04/08/2020.

 

 

 

 

 

 

Question Statement

 

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]

 

Pharmacist

pharm_id

name

username

password

number

address

Phrm001

ABC

abc@gmail.com

abc123

12345

Lahore

 

ShoppingCart

cart_id

cart_items

amount

Cart001

3

60

 

Users

user_id

name

Number

password

Address

cart_id

User001

ABC

12345

abc@321

Lahore

Cart001

 

CreditCard

card_number

expiry_date

Card001

2025-07-23

 

Payment

payment_id

amount

status

date

time

card_number

Pay001

60

Pending

2020-07-16

10:07:10

Card001

 

Orders

order_id

Medicines

total_price

Time

date

user_id,

pharm_id

payment_id

Ord001

Panadol

2000

10:00:00

2020-07-16

User001

Phrm001

Pay001

 

 

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]

 

User ID

Name

Phone No

Password

Address

Cart ID

User001

ABC

12345

abc@321

Lahore

Cart001

 

6)      Write a query that add a column named as “Label” of varchar data type having 15 size in the “Shelve” table.

[2 marks]

 

Note:

 

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.

 

 

 

 

 

 

 

Solution Guidelines:

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

 

Sample output:

For example, a select query is applied on the Payment table to retrieve all the records of the table, then:

 

 

Query

Execution snapshot

SELECT * FROM Payment;

 

 

 

=====================================End=======================================

 

For any query about the assignment, contact at CS403@vu.edu.pk

 

GOOD LUCK

 

Views: 1427

Replies to This Discussion

RSS

© 2021   Created by + M.Tariq Malik.   Powered by

Promote Us  |  Report an Issue  |  Privacy Policy  |  Terms of Service