-- write our SQL code here

DROP TABLE S; -- we need to drop S because it contains the foreign key restraint so we can then drop R

DROP TABLE R; -- will remove any data in R, as well as the strucutre itself



CREATE TABLE R (
    A INTEGER PRIMARY KEY, -- column-level constraint
    B CHAR(1) UNIQUE, -- canidate key
    C VARCHAR(10) -- similiar to string
);



-- (C, D) should be the composite primary key of S
CREATE TABLE S (
    C INTEGER,
    D CHAR(1),
    E VARCHAR(10),
    A INTEGER, -- S.A is a foreign key referencing R.A
    PRIMARY KEY (C, D), -- table-level contraint
    FOREIGN KEY sa_references_ra (A) REFERENCES R (A)
);

-- error because foreign key being refences must be a key and must have the same data type
CREATE TABLE F (
    F INTEGER, -- T.F is a foreign key referencing S.E
    FOREIGN KEY sa_references_ra (F) REFERENCES S (E)
);

INSERT INTO R (A, B, C) VALUES (1, 'b', 'test1');

-- NSERT INTO R (A, B, C) VALUES (1, 'c', 'test2'); -- expected to fail

INSERT INTO R (A, B, C) VALUES (2, 'c', 'test3');

-- INSERT INTO R (A, B, C) VALUES (3, 'c', 'test4'); -- expected to fail because of canidate key

INSERT INTO S (C, D, E, A) VALUES (10, 's', 'test', 1);

-- INSERT INTO S (C, D, E, A) VALUES (11, 's', 'test', 9); -- this should fail because no row in R where A is 9

-- displays tables
-- MySQL command
SHOW TABLES;

-- MySQL command
-- describe contents of table
DESC R; -- column names and data types for table R
DESCRIBE R;

-- SELECT is standard SQL command
SELECT * FROM R; -- show all rows, all columns from table R
SELECT * FROM S;