Competition - post a photo you've taken of nature >>

BTEC Level 3 IT Extended Certificate Unit 2 - April-May 2018

Hi, was wondering if anyone else will be taking this next week? Any tips on what to revise, what to expect etc? I am currently trying to revise normalization which I know is really important to this but haven't quite wrapped my head around it yet!

For those of you that have already done the exam, what specifically were you tested on? Anything you know will come up and stuff you don't really need? Thanks!

Reply 1

winterscoming

Original post by Fovyqapu

I am currently trying to revise normalization which I know is really important to this but haven't quite wrapped my head around it yet!

Try not to over-think the basic idea of Normalisation - you can find a lot of database jargon, notation and complex steps to describe the different stages of normalisation, but actually all it boils down to is just figuring out what entities need to exist in your system and how they're related.

Normalisation is all about simple logical reasoning - It takes some practice to get into the right mindset, but it ends up being plain common sense about 95% of the time. Whenever you get any kind of text which describes a problem (e.g. writing a database for a school, shop, club, airline, hospital etc.) then you need to scan through the problem and identify nouns because those are usually some of your tables, and just think about the problem from there.

For example:

Spoiler

By choosing the nouns in this description, you get a list of entities:

Spoiler

So just by reading that text without doing any kind of deep analysis related to Normalisation, you end up with a list of entities (i.e. table names). Remember that table names should normally be non-plural, so you'd have a list which looks like this:

•

Club

•

Member

•

Facility

•

Membership

•

Access_Level

•

Payment_Method

•

Payment

•

Activity

•

Subscription

That doesn't necessarily give you a complete picture, there are potentially more tables required if some of the tables above don't relate to each other very easily, or there may be some which are unnecessary (For example, "Club" might be unnecessary if there's only one club)

As a general rule of thumb, it's safe to assume that all entities have an 'ID' field which they use as their primary key. Beyond that, it's a matter of using common sense to decide which of the attributes belong to which entity, and then to understand the relationships between those entities, you need to understand how . the data is going to be used.

For example, the club may need to take a single payment which covers their subscription and an activity. You could reason that subscriptions would involve multiple payments, but furthermore, somebody might want to pay for multiple subscriptions at once. Therefore a many-to-many relationship exists and you need a "Join" table - e.g. Subscription_Payment. Similarly, if an activity is something like a fitness class for many members, then you could reason that members might also want to pay for many activities, so that's another many-to-many relationship needing a "Join" table - e.g. Activity_Payment.

For any database problem, you should be able to write up a list of entities and describe their relationships without doing anything more than just reading the problem, thinking about how the data is used and whether it makes sense for that data to be related.

Reply 2

Fovyqapu

Original post by winterscoming

Great post! I had thought that one-to-many relationships were the most common ones to come up in the exercises, but knowing now that many-to-many relationships need a separate table will be invaluable.

What about foreign and composite keys? Are those likely to factor in the relationships?

Reply 3

winterscoming

Original post by Fovyqapu

Well, a many-to-many relationship is a bit artificial because there aren't any direct joins between the two tables. SQL doesn't provide any way of creating a direct Many-to-Many join, that's why the join table is required in the middle (hence the name "join table" because it's just a "table of joins").

Many-to-Many means that you've got two "one-to-many" relationships using a join table. i.e. One-to-Many and Many-to-One allows you to represent a Many-to-Many Join.

e.g. Using the earlier example - with Subscription / Payment.

•

Subscription has many Subscription_Payment

•

Payment has many Subscription_Payment

•

Subscription_Payment is the "join" table sitting in the middle.

Subscription and Payment tables wouldn't be able to have any direct join to each other because that would be restrictive. The joins in a many-to-many situation are controlled entirely within the Subscription_Payment table.

In the simplest case, the Join table in the middle would just contain foreign keys to each of the tables on either side, which is how it permits 'many' Subscriptions to be related to 'many' Payments. each row in A join table is a different join between different records on either side. For example:

•

Subscription 1 joined to Payment 1

•

Subscription 1 joined to Payment 2

•

Subscription 2 joined to Payment 3

•

Subscription 3 joined to Payment 3

•

Subscription 4 joined to Payment 3

•

etc.

Original post by Fovyqapu

What about foreign and composite keys? Are those likely to factor in the relationships?

Firstly, A composite key is just a group of columns used to uniquely identify a row (as opposed to a single column).

Composite keys aren't strictly necessary for database design. Sometimes they're useful where they make sense, a "nice-to-have" even (although that is often subjective/debatable..). They can sometimes help improve the performance of the database in some scenarios. Overall though, even if composite keys weren't possible, then you would still be able to put your data into 3NF or BCNF. Composite keys and Foreign keys are orthogonal concepts.

Foreign keys are always required for joins. A foreign key can be a single foreign key column, or it can be a composite. A foreign key needs to match the identity of the table it's joining.

Of course, if you do have a table which uses a composite key to identify rows, then any joins to that table will need to use a foreign composite key against those identity columns.

Reply 4

winterscoming

Original post by driscoll2000

When you're talking about payment methods, why would their be two payment tables. I understand that members will need to pay for their fitness memberships but wouldn't that just fall under the payment methods table? I'm confused on what the second payment table is intended for, I'm assuming its for prices and arrangements. For example;It would cost '£29.99' a month, '£13.99 a week' or '£300.00 a year' and these costs would go into their separate table which is understandable. (This is a reply to winterscoming but it kept freezing when i tried to reply to that comment)

It was a bit of a contrived example, but normally I'd think of a payment being just be a record of an individual transaction which happens at a specific point in time - for example, a payment of £20 at 29/04/2018 22:00:00 - then there might be another payment later of £10 at 29/04/2018 22:10:00, etc.

A payment method is usually the way that payment happens - for example, Paypal, Credit Card, Direct debit, Cash, etc. Of course you could put that into the Payment table if the only thing you're recording is whether it's PayPal/Credit Card/etc. On the other hand, there could be standard information attached to those which affect the club's profit - for example, fixed bank charges for a credit card payment, or PayPal fees, etc.

Reply 5

Fovyqapu

Original post by driscoll2000

It's because the two tables that are joined by the second payment table (Subscription_Payments) have primary keys, in which the columns they represent can only have unique fields.

For example, in the Payments table:

Attachment not found

The PaymentID field is the primary key (you'd see this in Design View), so if I tried to enter 1, 2 or 3 in the next field it wouldn't let me.

In a conceptual context, this means that only one person could make a payment with each payment method once, which wouldn't make sense in real life, because lots of people are obviously going to be paying with credit cards.

The same thing can be said for the Subscription table where the SubscriptionID field is the primary key - there will obviously be more than one person having one type of subscription. Therefore you need a third table which acts as the join between these two tables in a many-to-many relationship:

This is made up of the foreign keys PaymentID and SubscriptionID (one-to-many from the left and many-to-one from the right) and you won't need a primary key since it uses fields from other tables anyway.

Hope I helped!

My exams not until much later today. I'd say the key things to practice up until then are normalization with different raw data sets (if you can find them), validation rules and making input forms to add stuff to the database.

In the meantime, here's the latest sample assessment that Pearson have uploaded onto their website - https://qualifications.pearson.com/en/qualifications/btec-nationals/information-technology-2016.coursematerials.html (under sample assessment material) - it's in the new format and tells you how the tasks are structured, how long they take etc. and is good practice material overall.