Scribd
Upload
16 views4 pages

Student Enrollment and Book Adoption

The document outlines the creation of a database for student enrollment and book adoption, including the structure of tables for students, courses, texts, enrollments, and book adoptions. It provides SQL statements for inserting data into these tables and includes various queries to retrieve specific information such as students by major, courses by instructor, and book titles for courses. The queries also allow for analysis of student enrollment patterns and course statistics.

Uploaded by

Sumanth Reddy
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
16 views4 pages

Student Enrollment and Book Adoption

The document outlines the creation of a database for student enrollment and book adoption, including the structure of tables for students, courses, texts, enrollments, and book adoptions. It provides SQL statements for inserting data into these tables and includes various queries to retrieve specific information such as students by major, courses by instructor, and book titles for courses. The queries also allow for analysis of student enrollment patterns and course statistics.

Uploaded by

Sumanth Reddy
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Program 6: Student Enrollment and Book Adoption Database

1. Table Creation

CREATE TABLE STUDENT (


StudentID VARCHAR(10) PRIMARY KEY,
Name VARCHAR(100),
Major VARCHAR(50),
BirthDate DATE
);

CREATE TABLE COURSE (


CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(100),
Instructor VARCHAR(100)
);

CREATE TABLE TEXT (


TextID VARCHAR(10) PRIMARY KEY,
Title VARCHAR(100),
Author VARCHAR(100)
);

CREATE TABLE ENROLL (


StudentID VARCHAR(10),
CourseID VARCHAR(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES STUDENT(StudentID),
FOREIGN KEY (CourseID) REFERENCES COURSE(CourseID)
);

CREATE TABLE BOOK_ADOPTION (


CourseID VARCHAR(10),
TextID VARCHAR(10),
PRIMARY KEY (CourseID, TextID),
FOREIGN KEY (CourseID) REFERENCES COURSE(CourseID),
FOREIGN KEY (TextID) REFERENCES TEXT(TextID)
);
2. Insert Statements

-- STUDENT
INSERT INTO STUDENT VALUES ('S001', 'Alice', 'Computer Science', TO_DATE('2000-01-
15','YYYY-MM-DD'));
INSERT INTO STUDENT VALUES ('S002', 'Bob', 'Electronics', TO_DATE('1999-03-20','YYYY-
MM-DD'));
INSERT INTO STUDENT VALUES ('S003', 'Charlie', 'Computer Science', TO_DATE('1998-11-
10','YYYY-MM-DD'));
INSERT INTO STUDENT VALUES ('S004', 'David', 'Mechanical', TO_DATE('2001-07-
25','YYYY-MM-DD'));
INSERT INTO STUDENT VALUES ('S005', 'Eva', 'Computer Science', TO_DATE('1999-05-
14','YYYY-MM-DD'));
INSERT INTO STUDENT VALUES ('S006', 'Fay', 'Electronics', TO_DATE('2000-12-05','YYYY-
MM-DD'));
INSERT INTO STUDENT VALUES ('S007', 'George', 'Computer Science', TO_DATE('1997-08-
30','YYYY-MM-DD'));

-- COURSE
INSERT INTO COURSE VALUES ('C001', 'Database Systems', 'Dr. Smith');
INSERT INTO COURSE VALUES ('C002', 'Data Structures', 'Dr. Johnson');
INSERT INTO COURSE VALUES ('C003', 'Operating Systems', 'Dr. Brown');
INSERT INTO COURSE VALUES ('C004', 'Networks', 'Dr. White');

-- TEXT
INSERT INTO TEXT VALUES ('T001', 'Database Management Systems', 'Raghu
Ramakrishnan');
INSERT INTO TEXT VALUES ('T002', 'Data Structures Using C', 'Reema Thareja');
INSERT INTO TEXT VALUES ('T003', 'Operating System Concepts', 'Silberschatz');
INSERT INTO TEXT VALUES ('T004', 'Computer Networking', 'Kurose and Ross');

-- ENROLL
INSERT INTO ENROLL VALUES ('S001', 'C001');
INSERT INTO ENROLL VALUES ('S001', 'C002');
INSERT INTO ENROLL VALUES ('S002', 'C002');
INSERT INTO ENROLL VALUES ('S003', 'C003');
INSERT INTO ENROLL VALUES ('S004', 'C001');
INSERT INTO ENROLL VALUES ('S005', 'C004');
INSERT INTO ENROLL VALUES ('S006', 'C003');
INSERT INTO ENROLL VALUES ('S007', 'C001');

-- BOOK_ADOPTION
INSERT INTO BOOK_ADOPTION VALUES ('C001', 'T001');
INSERT INTO BOOK_ADOPTION VALUES ('C002', 'T002');
INSERT INTO BOOK_ADOPTION VALUES ('C003', 'T003');
INSERT INTO BOOK_ADOPTION VALUES ('C004', 'T004');

3. Queries
1. List all students majoring in Computer Science.

SELECT * FROM STUDENT WHERE Major = 'Computer Science';

2. Find all courses taught by 'Dr. Johnson'.

SELECT * FROM COURSE WHERE Instructor = 'Dr. Johnson';

3. List all students enrolled in the 'Database Systems' course.

SELECT [Link], [Link]


FROM STUDENT S
JOIN ENROLL E ON [Link] = [Link]
JOIN COURSE C ON [Link] = [Link]
WHERE [Link] = 'Database Systems';

4. Find the courses along with the titles of textbooks adopted.

SELECT [Link], [Link]


FROM COURSE C
JOIN BOOK_ADOPTION B ON [Link] = [Link]
JOIN TEXT T ON [Link] = [Link];

5. Find the names of students enrolled in more than one course.

SELECT [Link]
FROM STUDENT S
JOIN ENROLL E ON [Link] = [Link]
GROUP BY [Link]
HAVING COUNT(*) > 1;

6. Display course names and the number of students enrolled in each course.

SELECT [Link], COUNT([Link]) AS NumStudents


FROM COURSE C
JOIN ENROLL E ON [Link] = [Link]
GROUP BY [Link];

7. Find the name and major of the youngest student.


SELECT Name, Major
FROM STUDENT
WHERE BirthDate = (SELECT MAX(BirthDate) FROM STUDENT);

8. Find the title and author of the book adopted for 'Operating Systems' course.

SELECT [Link], [Link]


FROM COURSE C
JOIN BOOK_ADOPTION B ON [Link] = [Link]
JOIN TEXT T ON [Link] = [Link]
WHERE [Link] = 'Operating Systems';

Common questions

Powered by AI

Course popularity is inferred by the number of enrollments in each course. The SQL query using the COUNT function across the ENROLL table, grouped by CourseName, provides the number of students per course, effectively indicating popularity . Extracting this data requires skills in structuring aggregate SQL queries to interpret enrollments as a measure of popularity.

Referential integrity is ensured by using foreign keys in the ENROLL and BOOK_ADOPTION tables. ENROLL references the STUDENT and COURSE tables through StudentID and CourseID, respectively, while BOOK_ADOPTION references the COURSE and TEXT tables through CourseID and TextID. This setup ensures that every enrollment and book adoption entry must correspond to existing entries in their respective referenced tables . Evaluating this requires understanding relational databases and foreign key constraints.

The students enrolled in more than one course are determined by counting the entries per student in the ENROLL table. The SQL query specifically groups student names and applies the HAVING clause to select those with a count greater than one . This reasoning requires understanding of SQL group functions and filtering criteria beyond straightforward selection.

The database schema adequately covers basic student information, course details, enrollment, and textbook adoptions. However, it could benefit from incorporating additional attributes like student email, contact information, course credits, and semester details for each course. These additions would enhance the schema's capacity for comprehensive academic records management . Evaluating improvements requires a deep understanding of database design principles and real-world application requirements.

The relationship between course enrollment and textbook adoption is managed through the COURSE table, which links both ENROLL and BOOK_ADOPTION tables via CourseID. The ENROLL table records which students take which courses, while BOOK_ADOPTION indicates what textbooks are required for these courses . This illustrates a one-to-many relationship between the COURSE, ENROLL, and BOOK_ADOPTION tables.

The database system enables reporting on instructional staff via the COURSE table which records course names and instructors. However, limitations include a lack of detailed staff profiles, such as contact details, office hours, or qualifications. Without additional tables for comprehensive staff data, reporting is constrained to elementary information . Evaluating this effectiveness requires an understanding of integrated database reporting mechanisms.

The database system tracks student progression through entries in the ENROLL table, which records each student's current courses. By nature, this setup may only imply progression indirectly, such as through analyzing enrollment patterns or courses completion over time via supplementary scripting or queries. There is no explicit progression tracking mechanism noted in the schema, which could be enhanced with additional features . Analyzing this involves understanding database operations beyond static data recording.

Analyzing the intersection of student majors and course enrollments may reveal trends such as students majoring in related fields enrolling in specific courses. A comprehensive review involves joining STUDENT and ENROLL tables to correlate majors with course enrollments and identifying patterns, like seeing Computer Science students predominately enrolling in Computer Science related courses . Identifying patterns requires skills in associative SQL joins and data interpretation.

Identifying the youngest student uses the SQL MAX function to determine the latest birth date, reflecting the youngest age. The query compares each student's birthdate to this maximum value to identify the corresponding student. This logic efficiently utilizes SQL’s aggregate function and filtering capabilities . Analyzing this logic necessitates adeptness with SQL query structures and understanding of aggregate functions.

Enrollment data directly impacts book adoption as the demand for textbooks is proportional to the number of students enrolled in the respective courses. Accurate enrollment forecasting enables better planning for textbook procurement and budget allocation for educational resources . Evaluating these implications requires insight into academic resource planning and its dependency on database-stored information.

You might also like