CREATE TABLE Instructor ( FName VARCHAR2(20), LName VARCHAR2(20), Email VARCHAR2
ID: 3823491 • Letter: C
Question
CREATE TABLE Instructor (
FName VARCHAR2(20),
LName VARCHAR2(20),
Email VARCHAR2(20),
Username VARCHAR2(20),
passwrd VARCHAR2(20),
PRIMARY KEY(Username, Email));
CREATE TABLE RegUsers (
FName VARCHAR2(20),
LName VARCHAR2(20),
Email VARCHAR2(20),
Username VARCHAR2(20),
passwrd VARCHAR2(20),
PRIMARY KEY (Username, Email));
CREATE TABLE Messages (
Dte DATE,
Subject VARCHAR2(20),
Email VARCHAR2(20),
SName VARCHAR2(20),
MSG VARCHAR2(300));
CREATE TABLE Dictionary (
words VARCHAR2(20),
picture BLOB,
video BLOB );
CREATE TABLE Categories (
CatId INTEGER,
CatName VARCHAR2(20),
CatPicture BLOB,
PRIMARY KEY (CatId));
CREATE TABLE Lst (
Lid INTEGER,
Lname VARCHAR2(20),
PRIMARY KEY (Lid) );
/*week entity takes primary keys from other relation_INSTRUCTOR&USERS??*/
CREATE TABLE Comments (
CId INTEGER,
Text VARCHAR2(200),
Cdate DATE,
Username VARCHAR2(20),
Email VARCHAR2(20),
PRIMARY KEY (CId, Username,Email),
FOREIGN KEY (Username, Email) REFERENCES RegUsers
ON DELETE CASCADE);
/*error with TIME DATATYPE*/
CREATE TABLEtest (
testNo INTEGER,
Lv VARCHAR2(20),
PRIMARY KEY (testNo) );
CREATE TABLE Question (
Qid INTEGER,
Qtext VARCHAR2(60),
Canswer VARCHAR2(40),
option1 VARCHAR2(40),
option2 VARCHAR2(40),
option3 VARCHAR2(40),
PRIMARY KEY (Qid) );
CREATE TABLE Answer (
Qid INTEGER,
ActAns varchar(40),
Email VARCHAR2(20),
Username VARCHAR2(20),
PRIMARY KEY (Qid, Username, Email),
FOREIGN KEY (Qid) REFERENCES Question,
FOREIGN KEY (Username, Email) REFERENCES RegUsers);
CREATE TABLE Has2 (
Qid INTEGER,
testNo INTEGER,
PRIMARY KEY (Qid, testNo),
FOREIGN KEY (Qid) REFERENCES Question,
FOREIGN KEY (testNo) REFERENCES test );
CREATE TABLE Has (
CatId INTEGER,
PRIMARY KEY (CatId),
FOREIGN KEY (CatId) REFERENCES Categories );
/*NEED TO CHECK WITH THE TA*/
CREATE TABLE Contact (
);
/*NEED TO CHECK WITH THE TA*/
CREATE TABLE Post (
Cid INTEGER,
PRIMARY KEY (CId),
FOREIGN KEY (CId) REFERENCES Comments );
/*NEED TO CHECK WITH THE TA*/
CREATE TABLE crte (
Lid INTEGER,
PRIMARY KEY (LId),
FOREIGN KEY (LId) REFERENCES Lst );
Registered users:
Find a list of words that:
Start from a specific alphabetic (for example words start with w).
In specific category (for example words in family category).
Can list the test they have taken with their scores.
Can list all their comments in the application.
Can view the list of words that they have been created previously.
Explanation / Answer
Hi,
Please find below the answer-
Ans 1- select words from dictionary where words like 'w%';
Ans 2- Could not find the scores table to get the scores of the test. Can you please tell which is the column to refer for scores.
Ans 3- Could not find any application table here. So I am asuming you have a table called as "Application" with below structure-
Application(Application_id,Name,Cid);
Query - select a. application_id,c.text from application a join comments c
on a.cid=c.cid
Ans 4- select l.lname from crte c join Lst on c.lid=l.lid
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.