CS504 ASSIGNMENT 2 SOLUTION 2023

CS504 Assignment 2 Solution 2023: A Comprehensive Guide for Students. Are you struggling to complete your CS504 Assignment 2? Do you find it hard to understand the concepts and theories covered in the course? Worry not, as this article will provide you with a comprehensive guide to help you understand the requirements of the assignment and provide you with an optimal solution. In this article, we will discuss the following topics:

Table of Contents

  1. Introduction
  2. Understanding the Requirements of CS504 Assignment 2
    • Question 1: Conceptual Database Design
    • Question 2: Logical Database Design
    • Question 3: Relational Database Design
  3. CS504 Assignment 2 Solution
    • Question 1: Conceptual Database Design Solution
    • Question 2: Logical Database Design Solution
    • Question 3: Relational Database Design Solution
  4. Conclusion
  5. FAQs

Introduction

CS504, also known as Software Engineering – I, is a course offered by various universities to their computer science students. The course focuses on the fundamental principles and practices of software engineering, including software development life cycle, project management, software requirements, and software design. The course is divided into two parts: theoretical concepts and practical applications.

One of the practical applications of CS504 is the CS504 Assignment 2. The assignment focuses on database design and requires students to complete three questions related to conceptual, logical, and relational database design. The assignment is intended to help students understand the concepts and theories covered in the course and apply them to real-world scenarios.

Understanding the Requirements of CS504 Assignment 2

Before we dive into the solution of CS504 Assignment 2, let’s first understand the requirements of the assignment. The assignment consists of three questions, and each question focuses on a specific aspect of database design. Let’s discuss each question in detail:

Question 1: Conceptual Database Design

The first question of CS504 Assignment 2 requires students to create a conceptual database design for a given scenario. The scenario describes a bookshop that sells books to customers. The students are required to identify the entities, attributes, and relationships between entities and create an ER diagram based on their understanding of the scenario.

Question 2: Logical Database Design

The second question of CS504 Assignment 2 requires students to create a logical database design for the same scenario as in question 1. The students are required to identify the functional dependencies and normalize the relations up to third normal form (3NF).

Question 3: Relational Database Design

The third question of CS504 Assignment 2 requires students to create a relational database design for the same scenario as in question 1. The students are required to create tables, define primary keys, foreign keys, and constraints, and provide sample data to demonstrate the functionality of the design.

CS504 Assignment 2 Solution

Now that we understand the requirements of the assignment, let’s discuss the solution of CS504 Assignment 2. We will provide a step-by-step guide for each question and explain the reasoning behind our solutions.

Question 1: Conceptual Database Design Solution

The first question of CS504 Assignment 2 requires students to create a conceptual database design for a books s00tore scenario. Our solution to the question is as follows:

Entities:

  • Book
  • Customer
  • Order
  • Publisher
  • Author

Attributes:

  • Book: ISBN, Title, Edition, Price, Publication Date, Publisher ID
  • Customer: Customer ID, First Name, Last Name, Address, Email, Phone
  • Order: Order ID, Customer ID, Book ID, Order Date, Quantity, Total Price
  • Publisher: Publisher ID, Name, Address, Email, Phone
  • Author: Author ID

Relationships:

  • A Book is published by a Publisher
  • An Order is placed by a Customer
  • An Order contains one or more Books
  • An Author writes one or more Books

ER Diagram:

The above ER diagram depicts the conceptual database design for the bookshop scenario. The entities and their relationships are represented using boxes and lines, respectively. The attributes are represented using ovals attached to their respective entities.

Question 2: Logical Database Design Solution

The second question of CS504 Assignment 2 requires students to create a logical database design for the same book store scenario as in question 1. Our solution to the question is as follows:

Functional Dependencies:

  • Book ID -> ISBN, Title, Edition, Price, Publication Date, Publisher ID
  • Publisher ID -> Name, Address, Email, Phone
  • Author ID -> First Name, Last Name

Normalized Relations:

  • Book (Book ID, ISBN, Title, Edition, Price, Publication Date, Publisher ID)
  • Publisher (Publisher ID, Name, Address, Email, Phone)
  • Author (Author ID, First Name, Last Name)
  • Customer (Customer ID, First Name, Last Name, Address, Email, Phone)
  • Order (Order ID, Customer ID, Book ID, Order Date, Quantity, Total Price)

The above functional dependencies and normalized relations are based on the given scenario. The Book relation is in 3NF, whereas the other relations are in 2NF.

Question 3: Relational Database Design Solution

The third question of CS504 Assignment 2 requires students to create a relational database design for the same book store scenario as in question 1. Our solution to the question is as follows:

Tables:

  • Book (Book ID, ISBN, Title, Edition, Price, Publication Date, Publisher ID)
  • Publisher (Publisher ID, Name, Address, Email, Phone)
  • Author (Author ID, First Name, Last Name)
  • Customer (Customer ID, First Name, Last Name, Address, Email, Phone)
  • Order (Order ID, Customer ID, Book ID, Order Date, Quantity, Total Price)

Constraints:

  • Primary Key:
    • Book: Book ID
    • Publisher: Publisher ID
    • Author: Author ID
    • Customer: Customer ID
    • Order: Order ID
  • Foreign Key:
    • Book: Publisher ID
    • Order: Customer ID, Book ID

Sample Data:

Book:

Book ID ISBN Title Edition Price Publication Date Publisher ID
B001 1234 Book1 1 20 2023-01-01 P001
B002 5678 Book2 2 30 2023-02-01 P002

Publisher:

Publisher ID Name Address Email Phone
P001 Publisher1 Address1 publisher1@mail.com 123-456-7890
P002 Publisher2 Address2 publisher2@mail.com 234-567-8901

Author:

Author ID First Name Last Name
A001 Author1 Last1
A002 Author2 Last2

Customer:

| Customer ID | First Name | Last Name | Address |

CS504 ASSIGNMENT 2 SOLUTION 2023

Solution will be available soon

See Also below past papers: