Question

For this assignment, pretend you have been hired by a school district to create an application enabling access to educational videos freely available on the Internet.

For examples of places where these videos are available, check out this page:

https://www.refseek.com/directory/educational_videos.html (Links to an external site.).

For this assignment, you are responsible for developing a database schema (both conceptual and physical) to support this application. The schema must support the following application features:

1. It should be possible to efficiently search the application for videos, filtering on the following characteristics:
   • Title (required)
   • Keywords (note that a video may have multiple keywords)
   • Length of Video (required)
   • Appropriate Grade Level (optional)
   • Video Source (i.e. which of the sites referenced on the page above houses the video. required.)
2. It should be possible for teachers to comment on videos. Each comment should have the following fields:
   • Timestamp (required)
   • Name of teacher (optional)
   • Rating (1 to 5 stars, optional)
   • Text of comment (required)

The submission for this assignment should include the following artifacts:

• An entity-relationship diagram for the conceptual schema. You can do this on paper or whiteboard and submit a photograph, or do it using diagramming software and submit a screen shot or PDF. (Please do not submit a file in the diagramming software's native format.)
• A physical schema in the form of SQL CREATE TABLE statements. Please submit the SQL and not the database file (or files) itself.
• SQL INSERT statements creating at least 3 sample video records, each of which should have at least 2 keywords. Note that you will need to insert into multiple tables for each record if the assignment was done correctly.

This assignment can be completed adequately using as few as four tables. Your schema may end up with more tables and that is fine, but if you end up with more than 7-8 tables there is a good chance you are overthinking things.

Answer 1

The query details are given below the ER diagram, you can add sensible data, I just updated some sample data only into the fields.

Following is the ERD diagram

Following is the create table details

Create query for the table SchoolDistProfile(to save the school district details.)

CREATE TABLE SchoolDistProfile (
SchoolDistID int(11) NOT NULL,
SchoolDistName varchar(100) NOT NULL,
PRIMARY KEY (SchoolDistID)
);

Create query for the table VideoProfile (to save the video basic details)
CREATE TABLE VideoProfile (
VideoID int(11) NOT NULL,
SchoolDistID int Not NULL,
VideoTitle varchar(100) Not NULL,
VideoLength Time Not NULL,
AppGradeLevel int DEFAULT NULL,
VideoSource varchar(100) DEFAULT NULL,
PRIMARY KEY (VideoID),
foreign key (SchoolDistID) references SchoolDistProfile(SchoolDistID)
);

Create query for the table VideoKeywordDet (to save the keyword details against the video)

CREATE TABLE VideoKeywordDet (
keywordID int(11) NOT NULL,
VideoID int NOT NULL,
keyword varchar(150) Not NULL,
PRIMARY KEY (keywordID),
foreign key (VideoID) references VideoProfile(VideoID)
);

Create query for the table VideoCommentDet (to save the comment details)
CREATE TABLE VideoCommentDet (
CommentID int(11) NOT NULL,
VideoID int NOT NULL,
CommentText varchar(1000) Not NULL,
CommentTimeStamp Time Not NULL,
TeacherName varchar(100) Not NULL,
Rating1to5 int DEFAULT NULL,
PRIMARY KEY (CommentID),
foreign key (VideoID) references VideoProfile(VideoID)
);

Query for Insert into table SchoolDistProfile:
INSERT INTO
   SchoolDistProfile(SchoolDistID, SchoolDistName)
VALUES
   (1,'Arizona Dist');
  
Query for Insert into table VideoProfile:
INSERT INTO
   VideoProfile( VideoID, SchoolDistID, VideoTitle, VideoLength, AppGradeLevel, VideoSource)
VALUES
   (1,1,'Story Video1','14:00','1','https://videosource.com/video123'),
(2,1,'Story Video2','12:00','5','https://videosource.com/video245'),
(3,1,'Story Video3','11:00','6','https://videosource.com/video689');
  

Query for Insert into table VideoKeywordDet:
INSERT INTO
   VideoKeywordDet(keywordID,VideoID,keyword)
VALUES
   (1,1,'Video1keyword1'),
(2,1,'Video1keyword2'),
(3,2,'Video2keyword1'),
(4,2,'Video2keyword2'),
(5,2,'Video3keyword1'),
(6,2,'Video3keyword2');
  

Query for Insert into table VideoCommentDet:
INSERT INTO
   VideoCommentDet(CommentID, VideoID, CommentText, CommentTimeStamp, TeacherName, Rating1to5)
VALUES
   (1,1,'video for lower grade students', '18:00',null,4),
(2,3,'Nice Video informative', '15:00','b',5);