Question Database Question again- DataTypes

[Bj]

Sorry for the never ending 20 Q's but

The database we are designing for class is based on a TV supplier. The scenario says that there can only be two of each type of channel available per package. ex, documentary, lifestyle, movie, news and sports. So I have the entity labeled Package, with the attributes, id, description, price, ext ext..., then DOC1, DOC2, LIFE1, LIFE2, MOVIE, MOVIE2, NEWS, NEWS2, SPORT, SPORT2.

Obviously this isn't very efficient, I would rather have an array.

This Oracle Data Types shows the data types, but I cannot find anywhere (in my book, or on the internet) where or how to make an array as a data type.

Oh and I guessed about the NUMBER(X,Y) but that's not quite right;

Fixed-point NUMBER
NUMBER(p,s)
precision p = length of the number in digits
scale s = places after the decimal point, or (for negative scale values) significant places before the decimal point.

Any thoughts?

 

[TJohns]

A quick Google came up with this...

A VARRAY is an array of varying size. It has an ordered set of data  elements, and all the elements are of the same datatype. Each element  has an index, which is a number corresponding to the element's position  in the VARRAY. The number of elements in a VARRAY is the "size" of the  VARRAY. You must specify a maximum size when you declare the VARRAY  type. 

For example: CREATE TYPE myNumType AS VARRAY(10) OF NUMBER;

This statement defines myNumType as a SQL type name that  describes a VARRAY of NUMBER values that can contain no  more than 10-elements.

Then use myNumType (or similar) as a column definition

But I wouldn't like doing it this way. That sort of arbitrary restriction (two channel limit) should be considered more of a "business rule" that a database design issue. What if the "client" comes back a week later with a new package that used 3 movie channels, or releases brand new p0rn channels - you'd have to go back & redesign your database! I'd create a "flat" table with one row per package+channel combination, and use a trigger event when inserting rows to count the total number of entries for the package and apply any restrictions then. By making more complex trigger rules you can even do things like a package that allows 3 movie channels ONLY if you've ordered 2 documentaries.

Trev

 

[SiberianTiger]

I agree with Trev that using a vectored datatype for keeping a relation between entities is extremely poor way of doing. By doing so, you terribly limit your structure's extendability and performance. From my viewpoint, you first have to clarify the task a bit more. The critical questions you should ask are:

• 
  [#]Does a customer always have to choose two channels in his package (can't she choose 0 or 1)?
  [#]Do they have to be different channels?
  [#]Do they have to be channels of different type?
  [#]Does the retailer want to make customer to pick from packages with pre-defined channels, or rather from individual channels?

Assuming answer to (1) is yes, you need a business rule to enforce having exactly two channels per package.
If (2) is 'yes' then you should provide for uniqueness of channel id per package.
If (3) is 'yes' also, then you must provide for uniqueness of channel type id per package.
Answering question #4 will give you idea of whether you have to store a package as a separate entity, or it's rather best to tie channel information straight to a customer record.

Assuming the answers are 'yes'-'yes'-'yes'-'package picking', I'd go with the following data scheme (consider natural joining by keyfield names for each two particular tables):

customers

PK|customer_id|NUMBER
|customer_name|VARCHAR2()
FK|pack_id|NUMBER

packages

PK|pack_id|NUMBER
|package_name|VARCHAR2()

the relation establishing table:

pack_contents

FK|pack_id|NUMBER|*
FK|ch_type_id|NUMBER|*
|no_in_pack|NUMBER|*
FK|ch_id|NUMBER|

Comments on the above:

• Impose a unique index on the fields marked with asterisk
• Make INS/UPD triggers (FOR EACH ROW) for no_in_pack field so it could only assume values in range (1,2)
• All fields are NOT NULL

channels

PK|ch_id|NUMBER
FK|ch_type_id|NUMBER
|channel_name|VARCHAR(2)

channel_types

PK|ch_type_id|NUMBER
|ch_type_description|VARCHAR2()

A sample SQL for selecting channels picked by a user would be:

SELECT c.customer_name,
       p.package_name,
       c.channel_name,
       ct.ch_type_description
FROM customers AS c NATURAL JOIN
     packages AS p NATURAL JOIN
     pack_contents AS pc NATURAL JOIN
     channels AS ch NATURAL JOIN
     channel_types AS ct
WHERE c.customer_name = 'JOHN DOE'

I wouldn't like to use much trigger code for complex business rules; they are most natural for use in data integrity checks only. It's best to use stored procedures if you must check preconditions of the existing data or do data modifications in joined tables.

 

[Bj]

I agree with Trev that using a vectored datatype for keeping a relation between entities is extremely poor way of doing. By doing so, you terribly limit your structure's extendability and performance. From my viewpoint, you first have to clarify the task a bit more. The critical questions you should ask are:

• 
  [#]Does a customer always have to choose two channels in his package (can't she choose 0 or 1)?
  [#]Do they have to be different channels?
  [#]Do they have to be channels of different type?
  [#]Does the retailer want to make customer to pick from packages with pre-defined channels, or rather from individual channels?

Assuming answer to (1) is yes, you need a business rule to enforce having exactly two channels per package.
If (2) is 'yes' then you should provide for uniqueness of channel id per package.
If (3) is 'yes' also, then you must provide for uniqueness of channel type id per package.
Answering question #4 will give you idea of whether you have to store a package as a separate entity, or it's rather best to tie channel information straight to a customer record.

Assuming the answers are 'yes'-'yes'-'yes'-'package picking', I'd go with the following data scheme (consider natural joining by keyfield names for each two particular tables):

customers

PK|customer_id|NUMBER
|customer_name|VARCHAR2()
FK|pack_id|NUMBER

packages

PK|pack_id|NUMBER
|package_name|VARCHAR2()

the relation establishing table:

pack_contents

FK|pack_id|NUMBER|*
FK|ch_type_id|NUMBER|*
|no_in_pack|NUMBER|*
FK|ch_id|NUMBER|

Comments on the above:

• Impose a unique index on the fields marked with asterisk
• Make INS/UPD triggers (FOR EACH ROW) for no_in_pack field so it could only assume values in range (1,2)
• All fields are NOT NULL

To the questions above;
[#]Does a customer always have to choose two channels in his package (can't she choose 0 or 1)?
Actually the customer chozes a package and they can have more than one package at a time. Each package contains a pre-set list of channels. 1-?14?

There are 2 channels for each type of show provided, ex life, news ext...


[#]Do they have to be different channels?
Well kindof. Each channel hosts the what will be defined later as programs showed. If the package includes the channel, then the customer can view that channel, if not then they dont get it.

[#]Do they have to be channels of different type?
Um what do you mean, different type?

[#]Does the retailer want to make customer to pick from packages with pre-defined channels, or rather from individual channels?
Yes the retailer wants to make customer pick a package rather than included channels.

So if that really rolls down to what you have for customer, package, and pack-contents...

I am not really sure how that makes it into an array-style type though. I have attatched the ERD in case that helps.

 

[SiberianTiger]

To the questions above;
Actually the customer chozes a package and they can have more than one package at a time. Each package contains a pre-set list of channels. 1-?14?

Given that, you'd need one more relation table to establish a many-to-many relation between CUSTOMER and PACKAGE at your ERD scheme. (and their mutual connection should have a crow paw at both ends)

[#]Do they have to be channels of different type?
Um what do you mean, different type?

I mean categories of the channels: sports, science, music, etc. The question was asking if a package could contain sports+sports or music+music, or it couldn't. I assumed that having two channels of the same category in the same package was denied.

So if that really rolls down to what you have for customer, package, and pack-contents...

As far as I can see, it does. Although you've got some more entities in your scheme whose requirements aren't known to me, so you are better to think my offered data structure over and see how well it could meet your requirements. I hope it gives a good jump start.

I am not really sure how that makes it into an array-style type though. I have attatched the ERD in case that helps.

I, in turn, am not sure why exactly do you want an array-style type. Are you required to use one by your tutor? If not, you better stop looking for a place to it. Your problem doesn't look like one which would benefit from using an array type.

 

[Bj]

Given that, you'd need one more relation table to establish a many-to-many relation between CUSTOMER and PACKAGE at your ERD scheme. (and their mutual connection should have a crow paw at both ends)

oh yes right. I cannot find a many-many relationship in viso, but I find that's because it doesn't exist. So duel relationships are needed, thanks for the tip.

I mean categories of the channels: sports, science, music, etc. The question was asking if a package could contain sports+sports or music+music, or it couldn't. I assumed that having two channels of the same category in the same package was denied.

Oh no, the package can have 1 or the other, or both.

As far as I can see, it does. Although you've got some more entities in your scheme whose requirements aren't known to me, so you are better to think my offered data structure over and see how well it could meet your requirements. I hope it gives a good jump start.

Well there are other requirements of the database as well and that's what the majority of those other entities are.

I, in turn, am not sure why exactly do you want an array-style type. Are you required to use one by your tutor? If not, you better stop looking for a place to it. Your problem doesn't look like one which would benefit from using an array type.

No I am not required to use an array from the Professor, actually, he gave it the go-ahead (after a few modifications.) I was just wondering if there was a better way of doing it.

So realistically what your saying is that listing it the way I did in the package entity, is the best way to do it?

 

[SiberianTiger]

No I am not required to use an array from the Professor, actually, he gave it the go-ahead (after a few modifications.) I was just wondering if there was a better way of doing it.

So realistically what your saying is that listing it the way I did in the package entity, is the best way to do it?

Well, in my opinion, neither way is optimal for this kind of database. Vector types cannot be used in SQL queries without tricks; Having much denormalized data (like in your version of Package entity) leads to unoptimal growth of storage space and much overhead when you try to update your data. Such constructs fit best into reporting only data warehouses, not OLTP type databases like yours.

I believe you are best off with having a separate entity for channels, linked to packages in the manner I proposed. But remember, exclusive solutions are rare in IT. Perhaps your Professor had some different considerations when agreeing to this (like making you learn vector and table types, for instance).

---------- Post added 03-02-10 at 00:24 ---------- Previous post was 02-02-10 at 22:40 ----------

Forgot to mention that given the following

Oh no, the package can have 1 or the other, or both.

You should rather make your pack_contents table like

pack_contents

FK|pack_id|NUMBER
FK|ch_id|NUMBER
|no_in_pack|NUMBER

(no ch_type_id reference here)

Define a unique index over all fields here. Make no_in_pack value assume only 1 or 2.