Help PLEASE! So I am super new to sql and I have an assignment and am looking for some feedback

Transact-SQL
1

So here is what I have so far.

DROP DATABASE IF EXSISTS CDDATA;
CREATE DATABASE CDDATA;
USE CDDATA;

CREATE TABLE CDs (
CDID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
CD1 VARCHAR(20) NOT NULL,
Price FLOAT(4) NOT NULL,
);

CREATE TABLE Customers (
CustomersID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
Name VARCHAR(40) NOT NULL,
PRIMARY KEY (CustomersID)
CONTRAINT CCD1
FOREIGN KEY CustomerCD (CustermersID)
REFERENCES CDs (CD1)
ON DELETE CASCADE
ON DELETE CASCADE
);

INSERT INTO CDs VALUES (1, "A Music CD", 2.44);
INSERT INTO CDs VALUES (2, "A Music CD", 3.23);
INSERT INTO Customers VALUES (null,
INSERT INTO Customers VALUES (null,
INSERT INTO Customers VALUES (null,

And this is what I have to do. Develop the following database:

  1. A table CDs with
    • title (String)
    • price in $ - floating point

  2. A table Customers with
    • name (String)
    • a primary key of a CD as a foreing key
    • the number of copies of this CD that the customer has purchased
    Provide surrogate keys for every table!
    Create SQL statements that model the following situations

  3. Customer "Gerald" bought 3 copes of "Thick as a Brick"

  4. Customer "Joe" bought 12 copies of "Dustbowl"

So mainly I have questions about surrogate keys and the SQL statements at the end. Thankyou for any and all help!

2

This forum deals with Microsoft SQL Server issues, and your code looks like you are using MySQL (auto_increment).

  • In table CDs you should probably use the name "title" instead of "CD1"
  • In table Customers you should add "number of copies"
  • Regarding surrogate keys (I don't know this term) I think you already dealt with this in creating "CDID" in table CD's - you "just" need to make it primary key
  • Regarding 3 and 4, you need to create insert statements into CDs table, creating "Thick as a Brick" and "Distbowl". Also create insert statements into Customers table, creating "Gerald" linking him to "Thick in a Brick" with 3 copies. Same goes with Joe