Enrolment was split - SQL

Example shows how relvars IS_CALLED and IS_ENROLLED_ON can be derived from the original ENROLMENT relvar, using projection in the initial assignment to those relvars. Here is how the same effect can be achieved in SQL:

Example: Splitting ENROLMENT

CREATE TABLE IS_CALLED

AS (SELECT DISTINCT StudentId, Name FROM ENROLMENT)

WITH DATA;

ALTER TABLE IS_CALLED ADD CONSTRAINT PRIMARY KEY ( StudentId );

CREATE TABLE IS_ENROLLED_ON

AS (SELECT DISTINCT StudentId, CourseId FROM ENROLMENT)

WITH DATA;

ALTER TABLE ADD CONSTRAINT PRIMARY KEY ( StudentId, CourseId ) ;

DROP TABLE ENROLMENT;

Explanation:

• CREATE TABLE IS_CALLED announces that what follows defines a base table named IS_CALLED.

• AS (SELECT DISTINCT StudentId, Name FROM ENROLMENT) specifies that the columns of ENROLMENT and their declared types are as in the specified expression.
• WITH DATA additionally specifies that the table resulting from the specified expression is to be the initial value of IS_CALLED.
• ALTER TABLE IS_CALLED ADD PRIMARY KEY ( StudentId ) specifies a constraint to the effect that no two distinct rows having the same StudentId value can ever appear simultaneously in IS_CALLED. Note that this constraint has to be given as a separate statement from the one that creates the base table. If the key word DISTINCT had been omitted, the CREATE TABLE statement would have succeeded but the ALTER TABLE statement would have failed because the required constraint would have been violated by the two appearances of the row for student S1, Anne.
• Similar comments apply to the CREATE and ALTER TABLE statements for IS_ENROLLED_ ON, but in the equivalent example noted that the specification KEY {StudentId, CourseId}, required by Tutorial D, is theoretically redundant because the entire heading is always a superkey. Here, the corresponding ALTER TABLE statement is not redundant because in the absence of any key constraints SQL allows the same row to appear several times simultaneously in the same base table.
• DROP TABLE ENROLMENT destroys the variable we have no further use for.

Posted Date: 1/18/2013 5:12:28 AM | Location : United States

Ask an Expert

Related Discussions:- Enrolment was split - sql, Assignment Help, Ask Question on Enrolment was split - sql, Get Answer, Expert's Help, Enrolment was split - sql Discussions

Write discussion on Enrolment was split - sql
Your posts are moderated

Write your message here..

Related Questions

Exception handling, Exception handling In the PL/SQL, a warning or erro... Exception handling In the PL/SQL, a warning or error condition is known as an exception. The Exceptions can be internally defined (by the run-time system) or user defined. The

In out mode - parameter modes, IN OUT Mode An IN OUT parameter passes ... IN OUT Mode An IN OUT parameter passes initial values to the subprogram being called and return efficient values to the caller. Within the subprogram, an IN OUT parameter acts

Theory of catastrophism or catalysm - origin of life, THEO R Y OF CATASTR... THEO R Y OF CATASTROPHISM OR CATALYSM (CUVIER 1769-1832) - The world has passed thorugh several stages and at the end of each stage there was a catastrophe killing all the

Data types in sql - character, Data Types in SQL - Character CHARACTER... Data Types in SQL - Character CHARACTER or, synonymously, CHAR, for character strings. When this type is to be the declared type of something (e.g., a column), the permissible

Using inner join, Using INNER JOIN INNER JOIN is used to retrieve the ... Using INNER JOIN INNER JOIN is used to retrieve the data from all tables listed based on a condition of equality listed after keyword ON. If the condition is not meet, rows ar

Defining and declaring collections, Defining and Declaring Collections T... Defining and Declaring Collections To create the collections, you must define a collection type, and then declare the collections of that type. You can define the VARRAY types a

Albeit simple method , Albeit simple method : These all the truth tabl... Albeit simple method : These all the truth tables give us our first as albeit simple method for proving a theorem: where check whether it can be written in propositional logic

Oracle 11 g new features , Oracle 11 G new features associated with this re... Oracle 11 G new features associated with this release:- Enhanced ILM  - Information Lifecycle Management (ILM) has been around for the almost 10 years, but Oracle has made

Name resolution-naming conventions, Name Resolution In potentially uncer... Name Resolution In potentially uncertain SQL statements, the names of the database columns take precedence over the names of the local variables and formal parameters. For e.g.

Parameter and keyword description - update statement, Parameter and Keyword... Parameter and Keyword Description:   table_reference: This keyword identifies the table or view that should be accessible when you execute the UPDATE statement, and for wh