Relational database concepts
The main vocabulary for database will be discussed in class. Specifically you need to understand the following concepts;- Relational Database concept (becomes more obvious as you set about creating one)
- Tables, Rows, Fields, Tuples and Attributes
- Primary and Candidate Keys
- Foreign keys
- Binary Relationships One to One, One to Many, Many to Many Relationships and how they work and how they are implemented
- How to interact with the data using SQL
Compulsory reading
Bruce Maxim SQL Design and Implementation (ppt) this power point discusses SQL which we will take a look at in class and use in the exercise below.
Other resources
Chapter 5 The Relational Database Model: Introduction this ppt will be used in class to illustrate the main concepts as it is well illustrated.SQL for Dummies Introduction The basic concepts of relational databases and how they work is covered here.
Exercise in class
Please use the vw_Aves_references worksheet in the file below. Discuss amongst yourselves what you think the tables are from which this view was created and the relationships between them. Create a spreadsheet in Excel for each of these table and the primary and foreign key field that join these.Note: this view doesn't show any of the key fields they are important to the database and programmer so you will have to add these yourself for your 40 records that you add to these tables.
We will go over this exercise in class after you have tried it for yourself so don't panic but I do suggest you read through what we went through in class carefully and look critically at the example in the readings to see how these relationships work. Later we will then do a join on the tables you created and see whether we get back to the records in the vw_Aves_references view from your mini normalized relational database.
Download xls
Invasive species database assignment
What you are expected to do for this assignment
- Designing a relational database for Invasive Alien Plant (IAP) spp
- Capture data into the database tables
- Provide two views on the data you have captured
1. Designing a relational database for IAP spp
Your database must be able to accommodate the following information for IAP spp
Design each table you will need in the database and include the following for each (see below);
Show the relationships between the tables indicating which field links the tables (see below)
Remember that when your database is normalized it will not contain any many to many relationship as these will have been transformed into two one to many relationships linking the tables via a relational or bridging table which not only contain the primary keys but also information belonging to the relationship itself... if you don't understand take another look at the readings
An example of you design in Word:
Database name: IAP_spp_information
Number of tables: 12
Table 1: tb_taxonomy
Field 1: id, integer, primary key
Field 2: species name, string
Field 3; genus_id, foreign key (1-m tb_genera.id [Table 2][Field 1])
Table 2: tb_genera
Field 1: id, integer, primary key (1-m tb_taxonomy [Table 1][Field 1])
Field 2: name, string
Make the main tables and relational tables as worksheets in excel.
This is what you must hand in on Tuesday 14 May. I will add the information for what I want you to do for the next two tasks
Your database must be able to accommodate the following information for IAP spp
- the taxonomy up to family level i.e. genera, families, species and sub-species level but does have to accommodate the higher taxonomy though we will be impressed if it does which means a better mark,
- common names for the IAP spp weell thought out way i.e. more than one common and per IAP spp,
- two types of information about IAP spp of your choice e.g. physical control methods and chemical control methods... you decide
- references for the information in the two types of information in well thought out way i.e more than one reference per item of information ( remember one reference may also refer to more than one item of information which make this relationship a what...)
Design each table you will need in the database and include the following for each (see below);
- the table name
- the field names indicating the type of data the will contain
- the primary key
- any foreign keys
Show the relationships between the tables indicating which field links the tables (see below)
Remember that when your database is normalized it will not contain any many to many relationship as these will have been transformed into two one to many relationships linking the tables via a relational or bridging table which not only contain the primary keys but also information belonging to the relationship itself... if you don't understand take another look at the readings
An example of you design in Word:
Database name: IAP_spp_information
Number of tables: 12
Table 1: tb_taxonomy
Field 1: id, integer, primary key
Field 2: species name, string
Field 3; genus_id, foreign key (1-m tb_genera.id [Table 2][Field 1])
Table 2: tb_genera
Field 1: id, integer, primary key (1-m tb_taxonomy [Table 1][Field 1])
Field 2: name, string
Make the main tables and relational tables as worksheets in excel.
This is what you must hand in on Tuesday 14 May. I will add the information for what I want you to do for the next two tasks
- Capture data into the database tables
- Provide two views on the data you have captured
No comments:
Post a Comment