Deliverable
In a PDF file ( LastName1_lastName2_ Milestone_2.pdf)
Names (your names ( as appears on Blackboard) with your student ID number)
Title (Title of the project)
URL (URL of the project) ( not providing these results in mark deduction)
( 3 marks) SQL script*
by referring to chapters 3-7 of text book ( and lecture notes) complete and finalize your previous design schema from milestone 1 and
– add constraints ( Unique, Primary Key, Foreign Key, Check, Default, Index, Auto Increment or any that applies)
– add correct datatype
(provide the whole SQL script which generates each table and enforces the constraints )
( 1 mark) ERD
provide the ERD of your design (generated by the client app you are using ) depicting the lines connecting your entities (crow foot etc depending on the client software you are using ) ( if the software you are using does not generate the crow foot or cardinality etc, you need to take your ERD to a paint application and add them manually)
(2 marks ) Data insertion
insert at least two entries in each of your tables
(provide the SQL statements for data insertion in text and the screenshot of the created rows with their values( in text or image) to prove data were inserted )
-2 marks will be deducted for each day late submission
-1 mark will be deducted for each missing requirement or mistake
-1 mark will be deducted if the font you are using is too tiny ( for ERD or screenshots)
*note: you can also define primary key or other constraints using alert table .
example ( not tested):
CREATE TABLE users (
userid int(11) NOT NULL,
first_name varchar(100) NOT NULL,
last_name varchar(100) DEFAULT NULL,
email varchar(100) NOT NULL,
password varchar(255) DEFAULT NULL,
….
)
CREATE TABLE user_details (
id int(11) NOT NULL,
userid int(11) NOT NULL,
roles longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
admin_activated tinyint(1) NOT NULL DEFAULT 0,
school varchar(256) DEFAULT NULL,
address varchar(256) DEFAULT NULL,
)
ALTER TABLE users
ADD PRIMARY KEY (userid );
ALTER TABLE user_details
ADD PRIMARY KEY (id),
ADD KEY userid (userid);
ALTER TABLE users
MODIFY userid int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE user_details
MODIFY id int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE user_details
ADD CONSTRAINT user_details_ibfk_1 FOREIGN KEY (userid) REFERENCES `users` (userid) ON DELETE NO ACTION ON UPDATE NO ACTION;
don’t forget to include all constraints, all foreign key constraints etc