Create table categories ( Categoriesid integer(20) Categoriesname varchar(20) Ca

Question

Create table categories ( Categoriesid integer(20) Categoriesname varchar(20) Categoriespicture Primary key Categoriesid Foreign key Categoriesid reference categories )

Create table comments ( Commentsid integer(20) Text varchar(20) Commentsdate date Primary key Commentsid Foreign key Commentsid reference comments )

create table dictionary ( word varchar(20) picture video )

create table list ( listid integer lname varchar(20) Primary key listid )

create table message ( date date subject varchar(20) Email varchar(20) s.name varchar(20) MSG varchar(20) )

create table record ( username varchar(20 Email varchar(20 f.name varchar(20 l.name varchar(20 password Primary key (username, Email ) ) ]

create table test ( testno integer Time Level varchar Primary key testno )

create table question ( Qid integer Qtext varchar(40) c.answer varchar(40) option 1 varchar(40) option 2 varchar(40) option 3 varchar(40) primary key Qid ) create table answer ( Qid integer Actual.answer varchar(40) Primary key Qid Foreign key Qid reference question )

create table has2 ( testno integer Qid integer Primary key (Qid, testno) Foreign key Qid reference question Foreign key testno reference test )

create table has ( Categoriesid integer(20) Primary key Categoriesid Foreign key Categoriesid reference Categories )

create table contact ( )

create table post ( cid integer primary key cid Foreign key cid reference comment )

create table create ( lid integer primary key lid Foreign key lid reference list )

create table instructor ( username varchar(20) primary key username Foreign key username reference records )

create table registereduser ( username varchar(20) primary key username Foreign key username reference records )

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

1.     Provide SQL statements to create all of the relations/ views in your database.

2.     Provide insert, update delete statements used in your database.

Explanation / Answer

Create table commands:

CREATE TABLE categories (
Categoriesid int PRIMARY KEY,
Categoriesname varchar(20),
Categoriespicture varbinary(max) -- Data type missing in question
-- Foreign key to self can be created but column anme should be different "Categoriesid Foreign key Categoriesid reference categories "
)
-----------------------------
CREATE TABLE comments (
Commentsid int PRIMARY KEY,
[Text] varchar(20),
Commentsdate date
-- Foreign key to self can be created but column anme should be different "Commentsid Foreign key Commentsid reference comments "
)
-----------------------------
CREATE TABLE dictionary (
word varchar(20),
picture varbinary(max),
video varbinary(max)
)
-----------------------------
CREATE TABLE list (
listid int PRIMARY KEY,
lname varchar(20)
)
-----------------------------
CREATE TABLE [message] (
[date] date,
[subject] varchar(20),
[Email] varchar(20),
[s_name] varchar(20),
[MSG] varchar(20)
)
-----------------------------
CREATE TABLE record (
username varchar(20),
Email varchar(20),
f_name varchar(20),
l_name varchar(20),
[password] nvarchar(100),
PRIMARY KEY (username, Email)
)
-----------------------------
CREATE TABLE test (
testno int PRIMARY KEY,
[Time] datetime,
[Level] varchar(20)
)
-----------------------------
CREATE TABLE question (
Qid int PRIMARY KEY,
Qtext varchar(40),
c_answer varchar(40),
option_1 varchar(40),
option_2 varchar(40),
option_3 varchar(40)
)
-----------------------------
CREATE TABLE answer (
Qid int,
Actual_answer varchar(40),
PRIMARY KEY (Qid),
FOREIGN KEY (Qid) REFERENCES question (Qid)
)
-----------------------------
CREATE TABLE has2 (
testno int,
Qid int,
PRIMARY KEY (Qid, testno),
FOREIGN KEY (Qid) REFERENCES question (Qid),
FOREIGN KEY (testno) REFERENCES test (testno)
)
-----------------------------
CREATE TABLE has (
Categoriesid int PRIMARY KEY,
FOREIGN KEY (Categoriesid) REFERENCES Categories (Categoriesid)
)
-----------------------------
-- Column details missing create table contact ( )
-----------------------------
CREATE TABLE post (
cid int,
PRIMARY KEY (cid),
FOREIGN KEY (cid) REFERENCES comments (Commentsid)
)
-- Definition of comment is missing
-----------------------------
CREATE TABLE [create] (
lid int,
PRIMARY KEY (lid),
FOREIGN KEY (lid) REFERENCES list (listid)
)
-------------------------------

CREATE TABLE instructor (
username varchar(20),
PRIMARY KEY (username)
-- ,Foreign key (username) references record(username) Reference cannot be created as username is not unique in record table
)
-----------------------------
CREATE TABLE registereduser (
username varchar(20),
PRIMARY KEY (username)
--,Foreign key (username) references record(username) Reference cannot be created as username is not unique in record table
)

===================================================================================

Insert commands:

INSERT INTO [dbo].[answer]
([Qid]
,[Actual_answer])
VALUES
(<Qid, int,>
,<Actual_answer, varchar(40),>)
GO

INSERT INTO [dbo].[categories]
([Categoriesid]
,[Categoriesname]
,[Categoriespicture])
VALUES
(<Categoriesid, int,>
,<Categoriesname, varchar(20),>
,<Categoriespicture, varbinary(max),>)
GO

INSERT INTO [dbo].[comments]
([Commentsid]
,[Text]
,[Commentsdate])
VALUES
(<Commentsid, int,>
,<Text, varchar(20),>
,<Commentsdate, date,>)
GO

INSERT INTO [dbo].[create]
([lid])
VALUES
(<lid, int,>)
GO

INSERT INTO [dbo].[dictionary]
([word]
,[picture]
,[video])
VALUES
(<word, varchar(20),>
,<picture, varbinary(max),>
,<video, varbinary(max),>)
GO

INSERT INTO [dbo].[has]
([Categoriesid])
VALUES
(<Categoriesid, int,>)
GO

INSERT INTO [dbo].[has2]
([testno]
,[Qid])
VALUES
(<testno, int,>
,<Qid, int,>)
GO

INSERT INTO [dbo].[list]
([listid]
,[lname])
VALUES
(<listid, int,>
,<lname, varchar(20),>)
GO

INSERT INTO [dbo].[message]
([date]
,[subject]
,[Email]
,[s_name]
,[MSG])
VALUES
(<date, date,>
,<subject, varchar(20),>
,<Email, varchar(20),>
,<s_name, varchar(20),>
,<MSG, varchar(20),>)
GO

INSERT INTO [dbo].[post]
([cid])
VALUES
(<cid, int,>)
GO

GO

INSERT INTO [dbo].[question]
([Qid]
,[Qtext]
,[c_answer]
,[option_1]
,[option_2]
,[option_3])
VALUES
(<Qid, int,>
,<Qtext, varchar(40),>
,<c_answer, varchar(40),>
,<option_1, varchar(40),>
,<option_2, varchar(40),>
,<option_3, varchar(40),>)
GO

INSERT INTO [dbo].[record]
([username]
,[Email]
,[f_name]
,[l_name]
,[password])
VALUES
(<username, varchar(20),>
,<Email, varchar(20),>
,<f_name, varchar(20),>
,<l_name, varchar(20),>
,<password, nvarchar(100),>)
GO

INSERT INTO [dbo].[test]
([testno]
,[Time]
,[Level])
VALUES
(<testno, int,>
,<Time, datetime,>
,<Level, varchar(20),>)
GO

UPDATE commands:

UPDATE [dbo].[answer]
SET [Qid] = <Qid, int,>
,[Actual_answer] = <Actual_answer, varchar(40),>
WHERE <Search Conditions,,>
GO

UPDATE [dbo].[categories]
SET [Categoriesid] = <Categoriesid, int,>
,[Categoriesname] = <Categoriesname, varchar(20),>
,[Categoriespicture] = <Categoriespicture, varbinary(max),>
WHERE <Search Conditions,,>
GO

UPDATE [dbo].[comments]
SET [Commentsid] = <Commentsid, int,>
,[Text] = <Text, varchar(20),>
,[Commentsdate] = <Commentsdate, date,>
WHERE <Search Conditions,,>
GO

UPDATE [dbo].[create]
SET [lid] = <lid, int,>
WHERE <Search Conditions,,>
GO

UPDATE [dbo].[dictionary]
SET [word] = <word, varchar(20),>
,[picture] = <picture, varbinary(max),>
,[video] = <video, varbinary(max),>
WHERE <Search Conditions,,>
GO

UPDATE [dbo].[has]
SET [Categoriesid] = <Categoriesid, int,>
WHERE <Search Conditions,,>
GO

UPDATE [dbo].[has2]
SET [testno] = <testno, int,>
,[Qid] = <Qid, int,>
WHERE <Search Conditions,,>
GO

UPDATE [dbo].[list]
SET [listid] = <listid, int,>
,[lname] = <lname, varchar(20),>
WHERE <Search Conditions,,>
GO

UPDATE [dbo].[message]
SET [date] = <date, date,>
,[subject] = <subject, varchar(20),>
,[Email] = <Email, varchar(20),>
,[s_name] = <s_name, varchar(20),>
,[MSG] = <MSG, varchar(20),>
WHERE <Search Conditions,,>
GO

UPDATE [dbo].[post]
SET [cid] = <cid, int,>
WHERE <Search Conditions,,>
GO

UPDATE [dbo].[question]
SET [Qid] = <Qid, int,>
,[Qtext] = <Qtext, varchar(40),>
,[c_answer] = <c_answer, varchar(40),>
,[option_1] = <option_1, varchar(40),>
,[option_2] = <option_2, varchar(40),>
,[option_3] = <option_3, varchar(40),>
WHERE <Search Conditions,,>
GO

UPDATE [dbo].[record]
SET [username] = <username, varchar(20),>
,[Email] = <Email, varchar(20),>
,[f_name] = <f_name, varchar(20),>
,[l_name] = <l_name, varchar(20),>
,[password] = <password, nvarchar(100),>
WHERE <Search Conditions,,>
GO

UPDATE [dbo].[test]
SET [testno] = <testno, int,>
,[Time] = <Time, datetime,>
,[Level] = <Level, varchar(20),>
WHERE <Search Conditions,,>
GO

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.