Relational Database advice

Ciaran219

Hi,

I am currently studying computer science through distance learning. the assignment that i am currently working on is to build a relational database. I am not sure where or how to begin to be honest. Below is what is needed for it, if anyone has any advice as to where or how to begin it would be greatly appreciated.

For this activity you are required to produce a database that has:
 Three relations with at least 10 to 20 rows each.
 Common fields that connect the relations.
 Two queries that represent a join and selection operation on the data.
You are responsible for figuring out the contents of each table, choosing the type of data for each field, selecting a key for each
relation, connecting the relations as needed, and entering the data.
The subject of your database is a computer science related database that should be constructed with the following
information:
 Programming languages and their inventors, including the paradigm (imperative, declarative, functional, etc.)

Just to add, i have contacted the college as they only provide theory and small screen shots, nothing which shows how to create a database or where to begin. They will give me screenshots etc after i submit for my 1st attempt out of 2.

Thanks in advance

Ciaran

Talisman

Have you learned anything about SQL?

You could install a database such as MySQL or PostgreSQL.

Use a database client such as HeidiSQL to create the database, tables etc.

Afterwards you can export the database as a nice SQL script.

Tom Dunne

It's quite a bizarre way of teaching database design.

Anyway, take a situation that you are familiar with and design a database for that. For example, a club membership database, a student database, a stock inventory database.

What exactly do you have to deliver? An ERD? DB Creation scripts? A design document?

Don't get distracted with SQL and installing databases for now, focus on your design.

Senna

 Three relations with at least 10 to 20 rows each.
 Common fields that connect the relations.

Do you understand this part?
Make a database comprising 3 tables, 10-20 records in all of them, primary and foreign keys to link them?


I'm in the same boat as you at the moment, just learning Databases & SQL part-time.
We have one class that is just database design, so pencil and paper is all thats required to create a database. Other class is SQL, so scripting the databases (and queries).
What have you been taught so far?

Ciaran219

hi,sorry i got emails for your replies last night but boards wouldnt load for me.
@Talisman, i have learned theoretical SQL, nothing in practical as of yet. I did start a basic course on code academy yesterday to show me the basics. But the database itself doesnt require sql, its only a simple basic one that i need to build.

@Tom Dunne & Senna I have to have it on computer science, specifically programming languages. I undertand the first section, as i have the correct number of tables now and the records of 10 languages with inventors data in the table. The foreign keys im not so sure of. I have what i think is the correct relationship connecting all three tables. The programming language is the common factor in all three tables, which i have a relationship attached and the line showing all the tables connected with this primary key.

The other thing im not 100% on is this line ** Programming languages and their inventors, including the paradigm (imperative, declarative, functional, etc.) ** My taking from this is all languages have one or more paradigms attached to them, and i have to enter a new row with this information into the table, i think thats what it means anyway.

Thanks
Ciaran

Buford T Justice

Ciaran219 wrote: »

hi,sorry i got emails for your replies last night but boards wouldnt load for me.
@Talisman, i have learned theoretical SQL, nothing in practical as of yet. I did start a basic course on code academy yesterday to show me the basics. But the database itself doesnt require sql, its only a simple basic one that i need to build.

@Tom Dunne & Senna I have to have it on computer science, specifically programming languages. I undertand the first section, as i have the correct number of tables now and the records of 10 languages with inventors data in the table. The foreign keys im not so sure of. I have what i think is the correct relationship connecting all three tables. The programming language is the common factor in all three tables, which i have a relationship attached and the line showing all the tables connected with this primary key.

The other thing im not 100% on is this line ** Programming languages and their inventors, including the paradigm (imperative, declarative, functional, etc.) ** My taking from this is all languages have one or more paradigms attached to them, and i have to enter a new row with this information into the table, i think thats what it means anyway.

Thanks
Ciaran

How are the relationships structured. Do languages have many inventors, one inventors or one, and likewise do inventors have many languages or just one etc... this will give you an idea of how to structure your foreign keys.

For examples if an inventor has many languages, then in the languages table you could have say, and inventor_id as the foreign key which corresponds to the id of the inventor in the inventor table. Generating a query to return all the languages invented by a particular inventor would go something like

SELECT * FROM inventors LEFT JOIN languages ON inventors.id = languages.inventors_id WHERE inventors.id = 1

Ciaran219

Buford T Justice wrote: »

How are the relationships structured. Do languages have many inventors, one inventors or one, and likewise do inventors have many languages or just one etc... this will give you an idea of how to structure your foreign keys.

For examples if an inventor has many languages, then in the languages table you could have say, and inventor_id as the foreign key which corresponds to the id of the inventor in the inventor table. Generating a query to return all the languages invented by a particular inventor would go something like

SELECT * FROM inventors LEFT JOIN languages ON inventors.id = languages.inventors_id WHERE inventors.id = 1

The relationship between them is that all 10 listed have created their own language. No one inventor has created more than one language in the list. I have set the primary key for each table as the ProgrammingLanguage Heading, and this is the link between them all. I think im mixing up what i mean. From what i can see online, what i have done is the foreign key ( relationship ) which links the three tables together.
ill get some screen shots and post them, may help show what i mean.

Ciaran219

this is the relationship i am talking about. The 3 tables i need to compress the image as its too big to enter here

Dev 17

Taken from /r/DevelEire

How a relational database works:

https://www.reddit.com/r/DevelEire/comments/3hy1km/how_a_relational_database_works/

Senna

Ciaran219 wrote: »

this is the relationship i am talking about. The 3 tables i need to compress the image as its too big to enter here

As I said above, I'm only learning databases at the moment too, so take my advice at your own risk!!

Firstly, name your tables something relevant to the data inside them.
You look to have 1 to 1 relationships, if all 3 tables are 1 to 1 then all the data should all be in just one.
Ideally you want a one to many structure.
I'm unsure what the righthand table holds, what is location and what is its relevance? Again, if a language has only one inventor and one location, that can go in the inventors table.

Ciaran219

Senna wrote: »

As I said above, I'm only learning databases at the moment too, so take my advice at your own risk!!

Firstly, name your tables something relevant to the data inside them.
You look to have 1 to 1 relationships, if all 3 tables are 1 to 1 then all the data should all be in just one.
Ideally you want a one to many structure.
I'm unsure what the righthand table holds, what is location and what is its relevance? Again, if a language has only one inventor and one location, that can go in the inventors table.

Ill be honest, we weren't given the information. We were told that it's based on programming languages and inventors and I must add this is plus anything else I'd like to add around this. I've also to add the paradigms associated with each programming language but I'm struggling to find all the info.
The location to be honest was something to buff up the table as I was struggling with info to add.
Yeah going for a one to many structure in it.

mightyreds

Why don't you make the third table your paradigm and post the key into the programing language table so if imperative is row one, under a paradigm column in programming language hold value 1 for imperative,so you'd have

Inventor
PrimaryKey
FName
SName
DOB

P Language
PrimaryKey
Name
Yearinvented
InventorKey
Paradigmkey

Paradigm
PrimaryKey
Name

Inventor key is posted to language as is paradigm. Memory's hazy on composite keys but language might use one here

Senna

Right so you have a table of paradigms, one for languages and one for inventors.
Each paradigm can have many languages.
Each inventor can have many languages, even if that's untrue, as it's not good to have a one to one relationship. Were you given the list of inventors as I'm sure some languages have credited two inventors?
You will have 2 foreign keys in languages, one the primary key from paradigms and one the primary key from inventors. That creates your relational db.

mightyreds

Senna wrote: »

Right so you have a table of paradigms, one for languages and one for inventors.
Each paradigm can have many languages.
Each inventor can have many languages, even if that's untrue, as it's not good to have a one to one relationship. Were you given the list of inventors as I'm sure some languages have credited two inventors?
You will have 2 foreign keys in languages, one the primary key from paradigms and one the primary key from inventors. That creates your relational db.

Snap!!

Ciaran219

So, any info that needs to be put into the table i had to source. I have the tables completed now and all foreign keys in the correct place. I am working currently on the SQL join & select. I keep getting a syntax error but for the life of me cannot find where the issue is. Below is the what i have.

Maybe it right in front of me, but cant find the issue. Error is " Missing Operator "

SELECT ProgrammingLanguage.InventorSecondName
LanguageInventors.InventorSecondName
FROM Programming Language
INNER JOIN Language Inventors
ON
Programming Language.InventorSecondName=Language Inventors.InventorsSecondName
ORDER BY
Programming Language.InventoSecondName

Senna

Are you using the right database, by default it's Master unless you either choose from drop down box.
Or write USE INVENTOR (or whatever its called) at top of script and execute it.

Ciaran219

Senna wrote: »

Are you using the right database, by default it's Master unless you either choose from drop down box.
Or write USE INVENTOR (or whatever its called) at top of script and execute it.

Im not sure to be honest. i just used the default sheet when i opened access. I had two lines missing letter but they werent pinging the issues, seems to be highlighting the first line when i try run it.

Senna

Is this the formate of your script :

Create Database x
Use x
Create table a
Create table b
Etc

Insert into a
Insert into b
Etc

SELECT..........

Ciaran219

This is my script.. I was getting an error in the 1st line which ive fixed. Im now getting a error in the "FROM" clause that ive marked in bold.

SELECT *FROM ProgrammingLanguage,
InventorSecondName,
LanguageInventors,
InventorSecondName
FROM ProgrammingLanguage
INNER JOIN LANGUAGE Inventors ON Programming LANGUAGE.InventorsSecondName=LANGUAGE Inventors.InventorsSecondName)
ORDER BY Programming LANGUAGE.InventorSecondName

Senna

Your first select statement was better, now you have just created more errors.

PM me the script where you created the database and tables, don't need the query scripts, I'll do my best, as i said, i'm only learning too.

Ciaran219

Senna wrote: »

Your first select statement was better, now you have just created more errors.

PM me the script where you created the database and tables, don't need the query scripts, I'll do my best, as i said, i'm only learning too.

Yeah im having a issue now because i have FROM in the sql twice now.. pm you now. thanks

Tom Dunne

Ciaran219 wrote: »

This is my script.. I was getting an error in the 1st line which ive fixed. Im now getting a error in the "FROM" clause that ive marked in bold.

SELECT *FROM ProgrammingLanguage,
InventorSecondName,
LanguageInventors,
InventorSecondName
FROM ProgrammingLanguage
INNER JOIN LANGUAGE Inventors ON Programming LANGUAGE.InventorsSecondName=LANGUAGE Inventors.InventorsSecondName)
ORDER BY Programming LANGUAGE.InventorSecondName

You have an orphan right bracket ) as I have bolded above.

Ciaran219

Tom Dunne wrote: »

You have an orphan right bracket ) as I have bolded above.

Thats been taken out now, tried two different queries.

1st one: error: "Missing Operation" 1st line
SELECT ProgrammingLanguage.InventorSecondName, LanguageInventors.InventorSecondName
FROM Programming Language
INNER JOIN Language Inventors
ON
Programming Language.InventorSecondName=Language Inventors.InventorsSecondName

2nd one : Error "From" clause error as i have two of them now.
SELECT
*
FROM,
ProgrammingLanguage,
InventorSecondName,
LanguageInventors,
InventorSecondName
FROM,
ProgrammingLanguage
INNER JOIN
LANGUAGE Inventors
ON Programming LANGUAGE.InventorsSecondName=LANGUAGE Inventors.InventorsSecondName
ORDER BY
Programming LANGUAGE.InventorSecondName

Senna

What data do you want returned? Just the secondname?


Does this work?

SELECT InventorSecondName FROM LANGUAGE Inventors

Ciaran219

Just the second name yeah. thats all thats needed for the assingment to be completed.

No im getting a " Syntax error FROM clause" error when i run that.

Senna

Then the table isn't call "LANGUAGE Inventors"

SQL server is different to MS Access, but look for the table name, I don't think it should be two words anyway.

Tom Dunne

Ciaran219 wrote: »

Thats been taken out now, tried two different queries.

1st one: error: "Missing Operation" 1st line
SELECT ProgrammingLanguage.InventorSecondName, LanguageInventors.InventorSecondName
FROM Programming Language
INNER JOIN Language Inventors
ON
Programming Language.InventorSecondName=Language Inventors.InventorsSecondName

2nd one : Error "From" clause error as i have two of them now.
SELECT
*
FROM,
ProgrammingLanguage,
InventorSecondName,
LanguageInventors,
InventorSecondName
FROM,
ProgrammingLanguage
INNER JOIN
LANGUAGE Inventors
ON Programming LANGUAGE.InventorsSecondName=LANGUAGE Inventors.InventorsSecondName
ORDER BY
Programming LANGUAGE.InventorSecondName

Golden rule when learning databases - don't use spaces in names of any database objects.

I'm assuming Programming Language is one table? Then rename it to Programming_Language or Prog_Lang for your own sanity.

jmcc

Tom Dunne wrote: »

Golden rule when learning databases - don't use spaces in names of any database objects.

I'm assuming Programming Language is one table? Then rename it to Programming_Language or Prog_Lang for your own sanity.

The other aspect is that while the commands and column names may be case insensitive, the table names are typically not. This means that getting the case of a letter wrong in a table name will cause an error.

Regards...jmcc

Ciaran219

hey, sorry I forgot to get back to everyone. In the end the issue was the Table Names. I had Programming Languages.. Once I changed this to Prog_Lang the query worked.

And on a happy side note, I got 100% in the assignment

Thanks everyone

Ciaran219

hey, sorry I forgot to get back to everyone. In the end the issue was the Table Names. I had Programming Languages.. Once I changed this to Prog_Lang the query worked.

And on a happy side note, I got 100% in the assignment

Thanks everyone