Scribd
Upload
13 views6 pages

SQL Database Setup for Adult Literacy

The document outlines a homework assignment involving the creation of a database named 'AdultLiteracy' with tables for Tutor, Student, MatchHistory, and TutorReport, including SQL commands for table creation and data insertion. It also includes instructions for adding a Math score to the Student table and a Subject to the Tutor table, as well as strategies for handling tutors teaching multiple subjects. Additionally, it provides a SQL command to identify tutors who have not submitted reports for July.

Uploaded by

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

SQL Database Setup for Adult Literacy

The document outlines a homework assignment involving the creation of a database named 'AdultLiteracy' with tables for Tutor, Student, MatchHistory, and TutorReport, including SQL commands for table creation and data insertion. It also includes instructions for adding a Math score to the Student table and a Subject to the Tutor table, as well as strategies for handling tutors teaching multiple subjects. Additionally, it provides a SQL command to identify tutors who have not submitted reports for July.

Uploaded by

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

HOMEWORK ASSIGNMENT 9

DHIR THACKER
002819144

1. Create a database named “AdultLiteracy” on your RDBMS environment. Using Figure 7-5 above, write DDL
commands to create table structures for each entity above. Name your tables with the following names:
Tutor, Student, MatchHistory, TutorReport.

CREATE DATABASE AdultLiteracy;


use AdultLiteracy;

-- create table Tutor


CREATE TABLE Tutor (
TutorID int not null,
CertDate date not null,
Status varchar(50) not null,
CONSTRAINT TUTOR_PK PRIMARY KEY (TutorID)
);

-- create table Student


CREATE TABLE Student (
StudentID int not null,
[Read] Float not null
CONSTRAINT Student_PK PRIMARY KEY (StudentID)
);

-- create table MatchHistory


CREATE TABLE MatchHistory (
MatchID int not null,
TutorID int not null,
StudentID int not null,
StartDate date not null,
EndDate date,
CONSTRAINT MatchHistory_PK PRIMARY KEY (MatchID),
CONSTRAINT MatchHistory_FK1 FOREIGN KEY (TutorID) REFERENCES TUTOR
(TutorID),
CONSTRAINT MatchHistory_FK2 FOREIGN KEY (StudentID) REFERENCES STUDENT
(StudentID)
);

-- create table TutorReport


CREATE TABLE TutorReport (
MatchID INT NOT NULL,
Month VARCHAR(20) NOT NULL,
Hours INT NOT NULL,
Lessons INT NOT NULL
CONSTRAINT TutorReport_PK PRIMARY KEY (MatchID, Month)
CONSTRAINT TutorReport_FK FOREIGN KEY (MatchID) REFERENCES
MatchHistory(MatchID)
);

2. Write SQL scripts to insert sample data from Fig 7-5 into the database.

-- insert values into table TUTOR


INSERT INTO TUTOR VALUES(100, '1/05/2008','Active');
INSERT INTO TUTOR VALUES(101, '1/05/2008','Temp Stop');
INSERT INTO TUTOR VALUES(102, '1/05/2008','Dropped');
INSERT INTO TUTOR VALUES(103, '5/22/2008','Active');
INSERT INTO TUTOR VALUES(104, '5/22/2008','Active');
INSERT INTO TUTOR VALUES(105, '5/22/2008','Temp Stop');
INSERT INTO TUTOR VALUES(106, '5/22/2008','Active');

SELECT * FROM TUTOR;

-- insert values into table STUDENT


INSERT INTO STUDENT VALUES (3000, 2.3);
INSERT INTO STUDENT VALUES (3001, 5.6);
INSERT INTO STUDENT VALUES (3002, 1.3);
INSERT INTO STUDENT VALUES (3003, 3.3);
INSERT INTO STUDENT VALUES (3004, 2.7);
INSERT INTO STUDENT VALUES (3005, 4.8);
INSERT INTO STUDENT VALUES (3006, 7.8);
INSERT INTO STUDENT VALUES (3007, 1.5);

SELECT * FROM STUDENT;

-- insert values into table MatchHistory


INSERT INTO MatchHistory (MatchID, TutorID, StudentID, StartDate) VALUES (1, 100,
3000, '1/10/2008');
INSERT INTO MatchHistory (MatchID, TutorID, StudentID, StartDate, EndDate) VALUES (2,
101, 3001, '1/15/2008', '5/15/2008');
INSERT INTO MatchHistory (MatchID, TutorID, StudentID, StartDate, EndDate) VALUES (3,
102, 3002, '2/10/2008', '3/01/2008');
INSERT INTO MatchHistory (MatchID, TutorID, StudentID, StartDate) VALUES (4, 106,
3003, '5/28/2008');
INSERT INTO MatchHistory (MatchID, TutorID, StudentID, StartDate, EndDate) VALUES (5,
103, 3004, '6/01/2008', '6/15/2008');
INSERT INTO MatchHistory (MatchID, TutorID, StudentID, StartDate, EndDate) VALUES (6,
104, 3005, '6/01/2008', '6/28/2008');
INSERT INTO MatchHistory (MatchID, TutorID, StudentID, StartDate) VALUES (7, 104,
3006, '6/01/2008');

SELECT * FROM MatchHistory;

-- insert values into table TutorReport


INSERT INTO TutorReport VALUES (1, '6/08', 8, 4);
INSERT INTO TutorReport VALUES (4, '6/08', 8, 6);
INSERT INTO TutorReport VALUES (5, '6/08', 4, 4);
INSERT INTO TutorReport VALUES (4, '7/08', 10, 5);
INSERT INTO TutorReport VALUES (1, '7/08', 4, 2);

3. Write the SQL command to add MATH SCORE to the STUDENT table.

-- 3. Write the SQL command to add MATH SCORE to the STUDENT table.

ALTER TABLE STUDENT ADD Math_score int;

-- adding values into column Math_score

UPDATE STUDENT SET Math_score = '90' WHERE StudentID = 3000;


UPDATE STUDENT SET Math_score = '80' WHERE StudentID = 3001;
UPDATE STUDENT SET Math_score = '74' WHERE StudentID = 3002;
UPDATE STUDENT SET Math_score = '69' WHERE StudentID = 3003;
UPDATE STUDENT SET Math_score = '85' WHERE StudentID = 3004;
UPDATE STUDENT SET Math_score = '97' WHERE StudentID = 3005;
UPDATE STUDENT SET Math_score = '83' WHERE StudentID = 3006;
UPDATE STUDENT SET Math_score = '92' WHERE StudentID = 3007;

SELECT * FROM STUDENT;

4. Write the SQL command to add SUBJECT to TUTOR. The only values allowed for SUBJECT will
be Reading, Math, and ESL.

ALTER TABLE TUTOR ADD Subject varchar(50) CHECK (Subject in ('Reading', 'Math',
'ESL'));

-- adding values into column Subject

UPDATE TUTOR SET Subject = 'Math' WHERE TutorID = 100;


UPDATE TUTOR SET Subject = 'Math' WHERE TutorID = 101;
UPDATE TUTOR SET Subject = 'Reading' WHERE TutorID = 102;
UPDATE TUTOR SET Subject = 'ESL' WHERE TutorID = 103;
UPDATE TUTOR SET Subject = 'Reading' WHERE TutorID = 104;
UPDATE TUTOR SET Subject = 'Math' WHERE TutorID = 105;
UPDATE TUTOR SET Subject = 'ESL' WHERE TutorID = 106;

SELECT * FROM TUTOR;


Running below query resulted in an error:

UPDATE TUTOR SET Subject = 'English' WHERE TutorID = 100;

5. What do you need to do if a tutor signs up and wants to tutor in both reading and math? (Don’t need to
write SQL).

These are 2 possible approaches to the problem:

1)
To do this, we can update the primary key of the Tutor table to be a composite primary key, consisting of
two columns: TutorID and Subject. Now, if a tutor wants to tutor in both math and reading, upon defining
a composite primary key (TutorID, Subject), a given TutorID can have two records in the table, for
subjects: Reading and Math respectively.

We can edit the primary key definition constraint on the Tutor table.

Old definition of Tutor table


CREATE TABLE Tutor (
TutorID int not null,
CertDate date not null,
Status varchar(50) not null,
Subject varchar(50) CHECK (Subject in ('Reading', 'Math', 'ESL'));
CONSTRAINT TUTOR_PK PRIMARY KEY (TutorID)
);

Updated definition of Tutor table


CREATE TABLE Tutor (
TutorID int not null,
CertDate date not null,
Status varchar(50) not null,
Subject varchar(50) CHECK (Subject in ('Reading', 'Math', 'ESL'));
CONSTRAINT TUTOR_PK PRIMARY KEY (TutorID, SUBJECT)
);

2)
For a tutor to be associated with multiple subjects, instead of creating a new table, we can simply redefine
the Subject column as an array or list to store multiple values. The constraint of the values to be one of
(‘Reading’, ‘Math’) or both would be in place but if the definition is of array/list, multiple values can be
stored.

6. Write the SQL command to find any tutors who have not submitted a report for July.

-- 6. Write the SQL command to find any tutors who have not submitted a report for
July.

SELECT [Link], [Link], [Link], [Link]


From Tutor tut
WHERE NOT EXISTS (
SELECT *
FROM MatchHistory MatchHis JOIN TutorReport TutRep ON [Link] =
[Link]
WHERE [Link] = [Link] AND Month='7/08'
);

Common questions

Powered by AI

Enforcing data types and constraints ensures that data meets specified conditions, enhancing reliability and integrity within the 'AdultLiteracy' database. For instance, integer types for IDs prevent invalid entries, date types ensure valid date formats, and CHECK constraints restrict permissible values for fields like 'Subject'. These rules prevent erroneous, inconsistent, or out-of-scope data from entering the system, thereby maintaining accurate and trustworthy datasets .

Using a single 'Subject' column with a CHECK constraint limits each tutor to one subject per record, complicating scenarios where tutors need to teach multiple subjects. This constraint restricts flexibility and could result in duplicated entries or the need for workarounds like composite keys or arrays. To address this, the schema could be modified to allow lists or arrays in the 'Subject' column, or create an associative table to relate tutors with multiple subjects, though these solutions introduce complexity in data management and querying .

The composite primary key in the 'TutorReport' table, consisting of 'MatchID' and 'Month', ensures each report entry is unique per tutoring session and month. This key prevents duplicate reports for the same tutor and match in any given month, thus maintaining precise and non-redundant reporting data .

The SQL command to identify tutors who haven't submitted a report for July is: SELECT tut.TutorID, tut.CertDate, tut.Status, tut.Subject FROM Tutor tut WHERE NOT EXISTS (SELECT * FROM MatchHistory MatchHis JOIN TutorReport TutRep ON MatchHis.MatchID = TutRep.MatchID WHERE tut.TutorID = MatchHis.TutorID AND Month='7/08'). The logic behind this query is to select tutors from the 'Tutor' table whose 'TutorID' is not present in any 'TutorReport' records for July, ensuring only those without submissions in July are retrieved .

Defining a composite primary key on the 'Tutor' table allows a tutor to have multiple records, each associated with a different subject. By using a composite key consisting of 'TutorID' and 'Subject', a tutor can sign up to teach both 'Reading' and 'Math', for instance, by having separate entries in the table for each subject, while maintaining their unique identification through the combined key .

Adding a 'Math_score' column to the 'STUDENT' table provides detailed performance data specific to math, permitting refined analysis of student abilities and progress. This enhancement allows for targeted educational strategies and can improve the overall utility of the database by enabling queries that specifically evaluate and track math proficiency across students .

Data consistency between the 'Student' and 'MatchHistory' tables is ensured by using primary and foreign keys. The 'StudentID' in 'Student' serves as a primary key, while in 'MatchHistory', it acts as a foreign key. This relationship enforces that all entries in 'MatchHistory' must reference an existing student in 'Student', ensuring that matches cannot be created for non-existent students and maintaining consistent data across tables .

Using an array or list for the 'Subject' column allows storing multiple subjects for a single tutor without altering the primary key structure. This approach simplifies querying for tutors with multiple subjects. However, it may complicate data integrity enforcement and require additional checks to ensure values conform to allowed subjects ('Reading', 'Math', 'ESL'). Array handling can also make SQL operations less efficient compared to normalized designs .

Foreign key constraints in the 'MatchHistory' table ensure that each 'TutorID' and 'StudentID' in a match record references existing entries in the 'Tutor' and 'Student' tables, respectively. This guarantees that all matches involve valid tutors and students, preventing orphaned records and maintaining referential integrity across tables .

Altering the 'Tutor' table to include 'English' as a subject in the 'Subject' column would violate the CHECK constraint, leading to errors as the constraint ensures data integrity by allowing only specified subjects. This change would necessitate modifying the constraint to include 'English', ensuring that new data aligns with the allowed values, thus maintaining integrity and reducing the likelihood of erroneous data entries .

You might also like