Database Systems (CE/IS/CY 170) Project Two
Introduction Scenario
Imagine you are an entrepreneur starting a new business. To manage your business
effectively, you need to create a robust database system. This database will help you
organize and maintain records of customers, orders, products, suppliers, and other essential
business information. Your goal is to design a comprehensive database that supports
various operations, including data entry, updating records, and generating reports. This
project will guide you through the steps of creating and managing a database, ensuring it
meets the needs of your business while adhering to best practices in database design and
management.
Submission Date: 5/08/2026
Important Notice: Copying work will attract zero marks, so think twice before sharing
your work with others. This is an individual assignment.
Project Questions
Part 1: Database Design
1. Database Creation:
- What is the name of your business? Create a database using your business name.
- Question: Provide the SQL statement to create the database.
2. Table Design:
- Design five tables relevant to your business. What are the tables you have created?
Provide a brief description of each table.
- Question: Define the schema for each table including primary keys and foreign keys
where applicable. Provide the SQL statements used to create each table.
3. Insert Records:
- Insert at least five records into each table.
- Question: Provide the SQL statements to insert five records into each table.
Part 2: Database Operations
1. Deleting and Adding Columns:
- Perform operations to delete and add columns in one of the tables.
- Question: Provide the SQL statements used to delete and add columns.
2. Deleting and Updating Records:
- Perform operations to delete and update records in one of the tables.
- Question: Provide the SQL statements used to delete and update records.
Part 3: Data Selection and Ordering
1. Select Statements:
- Use select statements for various forms of selection.
- Question: Write select statements to retrieve specific data from the tables. Provide at
least three different select queries.
2. Ordering Data:
- Use ordering by ascending and descending order.
- Question: Write SQL statements to order data in ascending and descending order
based on specific columns.
Part 4: Constraints and Functions
1. Constraints:
- Use at least four constraints in your table definitions (e.g., primary key, foreign key,
unique, not null, check).
- Question: Describe the constraints used in your table definitions and their significance.
2. Functions:
- Use functions such as SUM, AVG, MIN, MAX, DESTINCT, etc.
- Question: Write SQL statements using aggregate functions to perform calculations on
the data. Provide examples using at least three different aggregate functions.
Part 5: Join Operations
- Perform join operations between two or more tables.
- Question: Write SQL statements to perform inner join, left join, and right join
operations between the tables. Provide examples of each type of join.
Part 6: Wildcards and LIKE Statements
- Use LIKE statements with wildcards to filter records based on partial matches (e.g.,
names starting with "J", emails containing "@[Link]", etc.).
- Question: Write SQL statements using LIKE and wildcards to search for specific
patterns in your tables. Provide at least three examples using different patterns.
Part 7: Graphical User Interface
- Create a simple graphical user interface for your database system. The interface should
allow users to perform actions such as adding, updating, viewing, or deleting records.
- You may use tools such as Python, Java (Swing/JavaFX), or any other platform you are
comfortable with.
- Question: Design and briefly describe your GUI. Include screenshots or diagrams
where possible. Explain how your GUI interacts with the database (e.g., through SQL
queries, backend logic, etc.).