8.2 Notebook and Notes Tables into Database

[CHarvey820]

57 As a developer, I want to insert a notebook and note tables in the database so that the team can be on the same page about notebook and note attributes.

[CHarvey820]

Definition of Done:

• Notebook table entered into database
• Note table entered into database
• Connect each notebook to note/s Notebook: username (FK-to student table), course/title, isRetired (True/False) Note: course/title(FK- to notebook table), notetitle, date, notetext (500 char - 4 bytes varchar)

[kalilamoua]

Two tables created in Oracle SQL Database: Please note: isRetired is a char check instead of boolean -cannot be boolean in Oracle SQL Read more here: https://stackoverflow.com/questions/3726758/is-there-a-boolean-type-in-oracle-databases

CREATE TABLE Notebook( courseTitle varchar(50), isRetired char check (isRetired in ('Y' , 'N')), stuUsername varchar(25), primary key(courseTitle), constraint FK_notebook foreign key(stuUsername) references Student (username) );

CREATE TABLE Note( noteDate date not null, noteTitle varchar(50), noteText varchar(500), courseTitle varchar(50), primary key(noteTitle), constraint FK_note foreign key(courseTitle) references Notebook (courseTitle) );

[kalilamoua]

Tasks:

1. Create notebook and note in oracle sql database connection (DONE)

[kalilamoua]

Fix: Added in student username as foreign key (fk) to student username in database. In addition I dropped Note and re-created the table.

CREATE TABLE Note( stuUsername varchar(25), noteDate date not null, noteTitle varchar(50), noteText varchar(500), courseTitle varchar(50), primary key(noteTitle), constraint FK_note foreign key(courseTitle) references Notebook (courseTitle), constraint FK_studentRef foreign key(stuUsername) references Student (username) );

[Y1cao]

Database review of notebooks and notes are done by me. --Yang