Scenario to RDBMS in SQL Server

[tltanhueco1990]

Good day, everyone!

I have this new project where I need to turn these scenarios into a DB with ERDs by CREATE DATABASE/CREATE TABLE without typing any actual queries, but all tables are in a one-to-many relationship:

I need to set associated primary keys and foreign keys to each other. Here's what I've done so far (I decoded in a notepad first before I create a DB in SQL Server):

4.1:EMPLOYEE PARKING SPACE
PARKINGLOT(LotCode,SpaceNumber,Description)
EMPLOYEE(Name,Address,DateHired,OfficeNumber,CellNumber,Email)
VEHICLE(PlateNumber,VehicleState,Make,Model,Year,Color)

4.2:RESTAURANT REVIEW
USER(ScreenName,ActualName,Address,Email,PhoneNumber)
RESTAURANT(Name,Address,PhoneNumber,URL,Category)
REVIEW(Scale,DinnerCost,Comments)

5.4:ALUMNI OFFICE
ALUMNI[Name*,Address(StreetAddress,City,State,ZipCode),HomeNumber,CellNumber,Email]
DEGREE(StudentName,YearGraduated,Degree,Major,SchoolAttended)
UNIVERSITY(SchoolShortName-varchar(40),SchoolFullName-varchar(100))
GIFT(DonationDate,Donator,DonatedAmount)
- DEGREE(StudentName) FK to ALUMNI(Name)

5.5:MOTOR DEALERSHIP
SALESPERSON(Name*,EID*,DateHired,SellingDate*,SoldTo)
CUSTOMER[Name*,CID*,Address(City,State,ZipCode),PhoneNumber,Email,PurchasedModel,SalespersonName]
MODEL(Name*,EngineSize,DateIntroduced)
MODELSALE(ModelName,SerialNumber,DateSold,PriceSold,Purchaser,CID,Seller,EID)
- MODELSALE(Purchaser) FK to CUSTOMER(Name)
- MODELSALE(CID) FK to CUSTOMER(CID)
- MODELSALE(Seller) FK to SALESPERSON(Name)
- MODELSALE(EID) FK to SALESPERSON(EID)
- MODELSALE(ModelName) FK to MODEL(Name)
- MODELSALE(DateSold) FK to SALESPERSON(SellingDate)

FK (Foreign Key)
* PK (Primary Key)
---
Am I missing something? Did I do something wrong with the relations? There's the part where I need to set a relationship to, for example, the purchased model to the customer and salesperson's names, but the data shows there's a first name, middle name and last name which confuses me.

 

[arnelgp]

it's a though school assigment alright.

 

[tltanhueco1990]

it's a though school assigment alright.

Yes. I just needed guidance on this. I'm not actually asking for the right answers.

 

[CJ_London]

You need to work out the relationships and create a joining table if the relationship is many to many

you will also need to add primary keys to each table and foreign keys as required

 

[Pat Hartman]

1. Every table requires a unique primary key. Yours don't have one in all cases and some are incorrect. You can use a natural key if you have one. Otherwise use an autonumber. I never use a natural key if it takes multiple fields to enforce uniqueness. In that case, I use an autonumber PK and a unique index on the combination of natural key fields.
2. You also don't have FK's defined in all cases.
3. Never use function names or property names as column names. So, "Name" can cause some really bad problems in Access and "Year" will be a problem in any RDBMS that includes a Year() function.
4. 4.1 requires at least one more table.
5. 5.4 requires at least one more table plus probably some lookup tables. You haven't handled the address correctly.
5. 5.5 PKs and FKs are not correct
6. Using "Name" to mean multiple entities in 5.5 is just going to cause confusion. You may as well name all PK's PK. Name is also not useful as a PK given that duplicate names are more common than you might imagine.

And finally, you will save yourself a lot of memory work if you use the same name for a PK as for a FK whenever possible