SQL Server homework....anyone want to....

Discussion in 'Programming and Homework Questions' started by HopelesslyFaithful, Apr 1, 2014.

Thread Status:
Not open for further replies.
  1. HopelesslyFaithful

    HopelesslyFaithful Notebook Virtuoso

    Reputations:
    1,552
    Messages:
    3,274
    Likes Received:
    164
    Trophy Points:
    0
    So i am doing this database project for SQL server and i have to build someone else's database and they build mine. I am fairly lost on all this relationship stuff. When is it 1 to many, when/where do you put it as a FK. I understand the concepts generally but the actual application i am lost. I built a database diagram and built a database off of someone else's diagram but that doesn't mean they actually function. Getting something that doesn't run into data errors/operational issues is vastly different. I understand the concept of 4th normal form or whatever but how to build something from scratch that works is...well beyond me at this moment. Also doesn't help that out teacher is well....unhelpful. Been reading book asking classmates and googling and i finally got it to compile in sql sever but that doesn't mean it is made right though :/ Anyone can build a chair....but doesn't mean it was made right and will bear weight ^^

    Anyways if anyone can take a few mins and take a look at it that would be bomb (got to be careful these days...can't type that on a train or in an airport :p

    https://www.dropbox.com/sh/1o5ujphoum0j31v/GOqNM8PTaS
     
  2. Thierry19

    Thierry19 Coffee enthusiast

    Reputations:
    1,458
    Messages:
    1,543
    Likes Received:
    122
    Trophy Points:
    81
    It's pretty hard to analyze everything, but it seems your foreign key are not specified.. Lets take the table Patient Records, according to the business plan you want EmployeeNumber to be your FK2 and PatientIDNumber should be PK and FK1.
    You want to have something like that:
    Code:
    CONSTRAINT CanBeAnything FOREIGN KEY (EmployeeNumber)
    REFERENCES LOCATIONS(EmployeeNmber)
    
    Note that you don't even have the EmployeeNmber in your location table.
    For all your "smaller" tables like Nutrition, you have to reference the PatientIDNumber to the Patient Records table like so:
    Code:
    CONSTRAINT AnyName FOREIGN KEY PatientIDNumber
    REFERENCES PatientRecords(PatientIDNumber)
    
    See foreign keys as a way to link two pieces of information.
    Like your family and your laptop, in table Family you have [Name, Age, , location] and in table Laptop you have [Brand, Model, OS, owner].
    You will want to have a constraint like so when you create your Laptop table
    Code:
    FOREIGN KEY OWNER 
    REFERENCES Family(Name)
    
    So that you can link a laptop brand to its location, or the age of the owner...

    Your situation is similar, for a patient in Patients Records, you want to use foreign key to have access to the Patient Emergency Contact, its room, payment history, etc.
     
  3. HopelesslyFaithful

    HopelesslyFaithful Notebook Virtuoso

    Reputations:
    1,552
    Messages:
    3,274
    Likes Received:
    164
    Trophy Points:
    0
    i renamed some of the orginal names because one was conflicting like two tables had the same PK plus her naming system was unnecessary so you should see EmployeeID and PatientID as replacements

    Yea i didn't add any FK because i wasn't even sure if i did it right in my table and if she did it right so i skipped that part in hopes to find someone who could show me how because i didn't want to waste time typing in stuff that was wrong
     
  4. Thierry19

    Thierry19 Coffee enthusiast

    Reputations:
    1,458
    Messages:
    1,543
    Likes Received:
    122
    Trophy Points:
    81
    Two table can have the same PK, it's not a problem.
    In table location you would have:

    Code:
    CONSTRAINT X PRIMARY KEY(EmployeeNmber)
    CONSTRAINT Y FOREIGN KEY EmployeeNmber
    REFERENCES Employees(EmployeeNmber)
    
    I might be wrong in the syntax, I used MySQL last time I created a DB.
     
  5. HopelesslyFaithful

    HopelesslyFaithful Notebook Virtuoso

    Reputations:
    1,552
    Messages:
    3,274
    Likes Received:
    164
    Trophy Points:
    0
    what is the X and Y value supposed to be when i enter it?

    Would this work with what i did in regaurds to creating a unique PK for each table


     
  6. Thierry19

    Thierry19 Coffee enthusiast

    Reputations:
    1,458
    Messages:
    1,543
    Likes Received:
    122
    Trophy Points:
    81
    Yeah that would work.
    The X and Y are simply there as filler, you can name your constraint anything you want.
     
  7. HopelesslyFaithful

    HopelesslyFaithful Notebook Virtuoso

    Reputations:
    1,552
    Messages:
    3,274
    Likes Received:
    164
    Trophy Points:
    0
    can you make an exam from my database file because i really don't understand what you mean -_- when people say name anything it always throws me for a loop
     
  8. Thierry19

    Thierry19 Coffee enthusiast

    Reputations:
    1,458
    Messages:
    1,543
    Likes Received:
    122
    Trophy Points:
    81
    Well you want it to be logical so I suggest naming them something like this:

    Code:
    CONSTRAINT Location_PK PRIMARY KEY(EmployeeNmber)
    CONSTRAINT Location_FK FOREIGN KEY EmployeeNmber
    REFERENCES Employees(EmployeeNmber)
    
    Or [TableName]_[P/FK], it seems to follow the conventions.
     
Loading...
Thread Status:
Not open for further replies.

Share This Page