Building A Relational Database Querying The Database

Building A Relational Database Querying The Datadat

Implement the normalized tables using MS-Access. From the tables implemented, develop queries to display student ID and Lname who has ticket code number 2, display student Fname who has the highest fine of ticket, and show the number of tickets for each student. Each student is required to implement the tables and queries in Microsoft Access.

Paper For Above instruction

The assignment focuses on building a relational database and performing specific queries related to parking tickets data at Millennium College. This involves designing normalized tables to accurately represent the data, and then utilizing MS-Access to develop targeted queries that extract meaningful information from the database. The core tasks include creating a well-structured database schema, inserting the sample data, and executing queries to answer particular questions about the dataset.

Introduction

Relational databases are foundational to managing structured data efficiently, enabling users to store, organize, and retrieve information systematically. In this context, the data on parking tickets at Millennium College provides an excellent case for demonstrating database design principles and query development. Proper normalization ensures data integrity, reduces redundancy, and simplifies maintenance. The subsequent development of specific queries helps analyze the data to support decision-making and administrative tasks.

Database Design and Implementation

The initial step involves analyzing the provided data to identify the entities and attributes. The data contains information about students, their tickets, and related details such as ticket codes and fines. To normalize this data, we decompose it into several related tables, ensuring adherence to normalization rules—to at least the third normal form (3NF). This process reduces redundancy and improves data integrity.

Entities and Attributes

  • Students: Student ID, Last Name, First Name, Phone Number, License Number
  • Tickets: Ticket Number, Date, Code, Fine, Student ID (Foreign Key)

Table Schemas

Based on these entities, the following tables are created:

  1. Students:
    • StudentID (Primary Key)
    • LName
    • FName
    • PhoneNo
    • StLicNo
  2. Tickets:
    • TicketID (Primary Key, AutoNumber)
    • TicketNo
    • Date
    • Code
    • Fine
    • StudentID (Foreign Key)

Data Entry

Using MS-Access, these tables are populated with the sample data provided. The StudentID acts as a unique identifier for each student, and each ticket record is linked via the StudentID, establishing referential integrity.

Query Development

After establishing the database, specific queries are constructed to answer the questions:

1. Display Student ID and Last Name for tickets with code number 2

This query fetches the StudentID and LName fields for students who have a ticket with Code 2.

SELECT Students.StudentID, Students.LName

FROM Students


INNER JOIN Tickets ON Students.StudentID = Tickets.StudentID


WHERE Tickets.Code = 2;

2. Display the First Name(s) of the student(s) who has the highest fine

This query identifies the maximum fine amount and retrieves the FName(s) of the corresponding student(s).

SELECT FName

FROM Students


WHERE StudentID IN (


SELECT StudentID


FROM Tickets


WHERE Fine = (


SELECT MAX(Fine) FROM Tickets


)


);

3. Show the number of tickets for each student

This query counts the total tickets associated with each StudentID and displays the StudentID along with the count.

SELECT Students.StudentID, COUNT(Tickets.TicketID) AS TicketCount

FROM Students


LEFT JOIN Tickets ON Students.StudentID = Tickets.StudentID


GROUP BY Students.StudentID;

Conclusion

The systematic approach to designing a normalized relational database ensures efficient data management and retrieval. Leveraging MS-Access for creating tables and executing queries enables effective analysis of parking tickets data. The queries developed provide insights into specific aspects, such as identifying students with particular ticket codes, those incurring the highest fines, and understanding ticket distribution among students. This exercise underscores core database principles and their practical application in administrative data analysis.

References

  • Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377-387.

Related Assignments