YT YouTube2Text

YouTube2Text → Transcripts → Intermediate SQL/2

Intermediate SQL/2 — Transcript

by Data Base Management System - IITKGP · 4,612 words · 850 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00[Music]
0:16welcome to module 10 of database
0:19management systems
0:20we have been discussing about
0:22intermediate level features in sql
0:26and this is the second and closing
0:28module on that
0:31so ah
0:32we have in the last module
0:35ah
0:37talked about join expressions views and
0:39ah transaction in a bit
0:41in this module we will try to ah learn
0:44sql expressions that are responsible for
0:48maintaining the integrity
0:50of the database ah we have talked about
0:52integrity a little we will now see how
0:54explicitly integrity can be checked and
0:56how different kinds of integrity can be
0:58ensured through sql
1:00we will
1:02also ah talk about more data types we
1:05have seen the basic primitive data types
1:07and we had
1:08promised that we will talk more about
1:10the data types including user defined
1:12data types here and finally we will talk
1:15about a very important aspect of
1:17authorization as to who can do what in a
1:20database in
1:22through sql
1:24so this is the module outline we start
1:27with the integrity constraints
1:29integrity constraints ah
1:31guard against
1:32accidental damage to the database that
1:34is there are certain real world facts
1:37that must be ensured in the database all
1:39the time
1:40for example ah
1:42in in bank accounts we have a minimum
1:44balance that need to be maintained
1:47for
1:48a particular
1:50customer we might want that the
1:52customer's phone number must be present
1:55we may have certain age bar in terms of
1:59entering
2:00into certain memberships or certain
2:02employment and so on all these kinds of
2:05real
2:06world constraints need to be represented
2:10and maintained in the database and that
2:12is the purpose of the integrity
2:14constraint
2:15and
2:16if we we will first look at the issues
2:19of integrity constraint for a single
2:20relation and
2:23we have seen
2:25the use of not null and primary key
2:28ah we will also talk about what is
2:30unique and we will see how
2:33actually general constraints can be
2:35checked in terms of a check clause with
2:37a predicate p
2:40so not null we had seen this before we
2:42can while creating the
2:44database table create table we can
2:46specify a field to be not null and then
2:48in that case null values will not be
2:50allowed in those fields so there must be
2:52some value given
2:55you can say one or more attributes to be
2:58unique if you specify them to be unique
3:01that means that in any instance of the
3:04table in future
3:07there cannot be two tuples which match
3:10in all of those attributes so if we say
3:12a one to a m are unique that means that
3:15if we have two
3:16different tuples in the table any time
3:18in future
3:19two different
3:21rows in the table t one and t two then
3:25across a one a two a m
3:27they must differ in at least one
3:30attribute value so uniqueness is a basic
3:34requirement for ah being a candidate
3:38key
3:38ah but they are still permitted to be
3:41null which in contrast to what is ah
3:44true for primary key you already know
3:46that primary key values cannot be null
3:48but uniqueness allows null values but
3:52they have to be different
3:56the check clause
3:58is
3:59where you
4:00say check and then you put a predicate
4:03so the idea is like this suppose i know
4:06that
4:07i have
4:08specified an attribute called semester
4:11it is a vercare 6 which means that it
4:13can have a
4:14string maximum of length 6 but naturally
4:17i can write anything there
4:19i can
4:20write morning in that
4:22field i can
4:24write welcome in that field and so on
4:26but those are not valid names of
4:28semesters so i want that
4:31in my design semester must have
4:35any of these
4:37ah values only
4:38so we say semester in so i have listed
4:42the values that are allowed in is a set
4:44membership so it says that the semester
4:46in so which means that the value of the
4:48semester is be one of these four
4:50and this whole thing
4:51this whole thing now becomes the
4:53predicate p
4:55which on which i do a check which means
4:57that whenever i am creating ah once we
5:00have created the table when i want to
5:02insert or update the values in the
5:05in the table the records in the table
5:07then e the value of semester has to be
5:10always within this otherwise the check
5:12integrity constraint will fail and the
5:15update or insert will not happen and an
5:18exception will be raised
5:20this is the basic idea of check
5:21constraints
5:23now let us move on to a
5:25more involved integrity check which goes
5:28beyond one table
5:30so let's suppose that we are talking
5:32about the instructor table we have the
5:35instructor table
5:37and instructor table has a department
5:41name
5:43similarly we have a department table
5:48department table which naturally has a
5:50department name now we know that this is
5:53the
5:54the key
5:55in the department table and therefore
5:57here it is a foreign key
5:59now
6:00while we are inserting records in the
6:02instructor table how do we guarantee
6:05that the record that we insert has a
6:07corresponding entry in the
6:11foreign key table that is reference
6:13table
6:15so it is when we are
6:17inserting an in
6:18a
6:19faculty in the saying that the faculty
6:22belongs to biology department there
6:24needs to be a biology entry in the
6:27department table as well
6:29so this is known as referential
6:31integrity that is once you refer from
6:34one table to the other
6:35that reference must also be a valid one
6:39otherwise your all your computations
6:41will go wrong
6:44so
6:45this is uh for saying it ah formally
6:48there are two relations and
6:50one relation as a primary key which is
6:52used in the other relation as a foreign
6:54key
6:55then there is a referential integrity
6:57that needs to be maintained
7:00so
7:01here we are just showing the effect of
7:03that so we have created a table
7:05this is the first one is what you have
7:08seen earlier creating the table course
7:11and that
7:12cable course needs the name of the
7:15department so we are specifying that it
7:17references the department table
7:20now if it references the department
7:22table it must ensure the
7:25referential integrity so this just says
7:28that
7:29this
7:30refers to the department table but i can
7:32be more specific to say
7:35what will happen
7:36if the integrity gets violated
7:39for example i have
7:42created this and the course
7:45table has an entry which has a
7:47department name say biology
7:50naturally biology department should have
7:53the entry there should be an entry in
7:56the
7:57department table
7:59with this ah department name biology for
8:02this to be valid now say for some reason
8:05ah the biology department is
8:08abolished and that particular record
8:10from the
8:12department table is removed naturally
8:15the course which is referring to
8:17biology in terms of its department name
8:20that particular record will become
8:22invalid
8:24so we can say that on delete what you
8:26should be doing
8:28one most common action that we specify
8:31in referential integrity is cascade that
8:35if the
8:36referred entity is deleted then the
8:38referring entity should also be deleted
8:40so if you delete the biology
8:43entry from the department table then all
8:46courses which have biology
8:49through references to department as
8:51their field value should also get
8:52deleted similar thing can
8:56be there on update also for example
8:59biology department
9:00say tomorrow changes the name to
9:02bioscience
9:04now if i have an
9:07referential integrity put on the course
9:10table as on update cascade then as i
9:13change the bioscience the name to
9:15bioscience all records in the
9:19table course which had the department
9:22name as biology will necessarily get
9:25updated so this is a way to maintain
9:29referential integrity cascading is is
9:32one of the most common way to handle
9:34this but there could be other ways to
9:38take action also there could be no
9:39action that you say ok i do not care let
9:42that happen in that case some
9:45because of the violation there could be
9:47some
9:48exceptions thrown or you can say that if
9:51this happens then i will set that field
9:52to null or i will set that field to some
9:54default value and so on
9:57so this is how the referential integrity
9:58has to be
10:00handled there could be integrity
10:02violation during transactions ah also
10:05this is an example of a
10:07self referential
10:09table which of persons which where every
10:12person's entry needs
10:14the name of the mother and the father
10:16which are also entries in this table so
10:19necessarily if you are entering a
10:20personal record you need these fields to
10:23be ah
10:25populated and that can be populated only
10:27if those records already exist so there
10:30is some order in which you have to enter
10:32the records or
10:33ah you have to set them as null and then
10:37update them in future and
10:39or
10:40some ways to say that well do not check
10:42this
10:44integrity now we will talk about this
10:46integrity at a later point of time so
10:48these are the issues that ah necessarily
10:50will have to be addressed
10:53ah let us move on and look at the sql
10:56data types and
10:58schemas so
11:00in addition to the data types like care
11:02where care ah
11:04int and all that you have an explicit
11:08date data type which
11:10ah gives you a
11:12year month date kind of ah format with a
11:15four digit year because date is very
11:17frequently required you have a time
11:20type ah to give you hour minute second
11:23time format
11:24ah
11:25you have a time stamp which is date and
11:28time together
11:29and you have what is known as interval
11:32where you can
11:34do a date or time difference between two
11:37different dates two different time to
11:39different time stamps and so on so these
11:42are the common added built in types
11:44which
11:45makes it very easy to handle the
11:48temporal aspects in sql queries
11:52in addition
11:54the next that you can do is you can
11:56create an index so let us
12:00look at this so this create table
12:03definition you understand
12:05well by now
12:06you can i can do this i can say create
12:09index
12:11and give a name for the index
12:13and specify which field on which
12:16the index should be created so here we
12:19are saying that the index should happen
12:22here
12:23this is the name of the relation this is
12:25the name of the attribute name of the
12:27field
12:28now this does not change any data
12:30neither does it change any schema but it
12:33creates certain
12:35additional structures so that it becomes
12:38easier to search
12:40this particular table using ids so if i
12:44have a query like this
12:46ah that i am trying to find out all
12:49information about a particular student
12:51then as we have said that by default the
12:56different entries the rows of a relation
12:58are unordered so the only way to find
13:01out this particular row and in fact
13:03whether it actually exist would be to go
13:06over all the relations one by one
13:09but if we index it then it creates some
13:12kind of a
13:13ah efficient data structure through
13:15which it can be
13:17searched out
13:18very efficiently very easily with the
13:20later ah
13:22module we will talk about indexing ah
13:24but just to give you the idea that this
13:27is similar to finding out a value in an
13:29unordered array if you are thinking of c
13:32in contrast i can
13:34we we all know that this can be done but
13:36takes a whole lot of time it takes order
13:38and time but
13:40i could
13:41keep those numbers in terms of a
13:45say some binary search tree balance
13:47binary search tree like red black tree
13:49or
13:51two three four tree kind of
13:53where the search can be conducted in a
13:55login time or i could keep it in terms
13:58of some efficient hashing mechanism
14:01where the search could happen in terms
14:02of an order one time also so indexing
14:05has a lot of
14:06importance and we will talk about that
14:09more but this is how you create index
14:12in sql
14:15you can have user defined types you can
14:17say create type and ah use ah
14:20some specific ah
14:23you know sub types of a type
14:25ah as and give it a name so its a
14:27numeric twelve
14:29so which is a 12 digit
14:31number with 2 decimal places of
14:35precision you can call it taller and
14:37then use that as a type name so type
14:40name
14:41doing this helps in two ways it makes
14:43sure that wherever you actually have to
14:45conceptually refer to dollars you are
14:47talking about dollar so its easier to
14:49understand and you are making sure that
14:51ah everywhere the same numeric precision
14:54is used
14:56you can also
14:57actually go further and
14:59create domains
15:01ah which is very similar to create type
15:03but domains are
15:05more powerful in the sense that in a
15:07domain you can also add
15:09constraints like not null and you say
15:12that this is person name so you say that
15:14this once you have
15:16said that
15:17this
15:18person name is 20 character long and it
15:21cannot be null then you do not
15:23specifically have to every time you
15:26define a field based on this domain type
15:29you do not have to specifically say that
15:31it is ah not null you could also create
15:34specific constraints in terms of the
15:36check clause and make it easier so now
15:40if you say degree level you do not have
15:41to put check clause explicitly in the
15:43sql query because it is already
15:46specified in the created domain sql
15:48supports certain large objects which
15:52are either called blobs if they are
15:53binary or called clob if they are
15:56character objects
15:57ah the only
15:58the major difference in terms of the
16:00large object types are they are not
16:02stored as a part of the table they are
16:04stored elsewhere and you actually
16:06maintain a
16:07kind of a reference a pointer to that
16:09large object
16:11so this is very useful in terms of
16:13handling photos videos and you know big
16:15binary files character files also
16:19let us
16:20move to authorization
16:22next
16:24authorization
16:25is
16:26the process by which you restrict
16:29different users to be able to do
16:32different kind of operations
16:34you would recall in the early ah
16:37modules on database
16:39overview we
16:41mentioned that there could be several
16:43types of users for a database there
16:45could be
16:46ah absolutely
16:48application users who
16:51necessarily do not feature as a part of
16:52the database development
16:54but there could be application
16:56developers ah expectedly most of you
16:58would become application developers ah
17:01there could be intermediate higher level
17:03of analysts who design databases design
17:06constraints ah decide on indexes and so
17:08on and that could be database
17:10administrators
17:11and also in terms of different
17:13application programs and programmers
17:16there is a need to separate out who can
17:19access which part of the database for
17:20example if you look at it at a banking
17:23system then
17:24while i am
17:27my net banking application is accessing
17:31different information about my account
17:33one part i need to ensure that i can
17:35only access my account
17:37and also what
17:38the database system needs to ensure
17:41is that a net banking application should
17:43in no way be able to access the
17:47information about the specific employees
17:49because in the same database information
17:52about the bank employees will also be
17:54there
17:54it should not be possible for
17:57possible to access the information about
17:59different ah
18:01physical information about the branches
18:03as to where
18:04ah how many square feet of area that
18:06branch has and so on so forth
18:09so we need to put variety of
18:11restrictions and
18:12as we will see that
18:14authorization or this process of
18:18restricting or allowing
18:20different
18:22access and different
18:23authority to operate
18:25is decided based on two different
18:28factors one is
18:30what you want to do
18:32and two is who wants to do that so what
18:34and who so we identify
18:37ah different
18:39operations or different operations on
18:42certain tables
18:44or operations on certain attributes as
18:47what needs to be done
18:50and on the other side we will identify
18:52who in terms of specific individual
18:55user ids
18:56or groups of user ids or roles that
19:00exist
19:01so here we will just try to show you how
19:03we can do that in in sql so the first
19:07part of the authorization is being able
19:09to
19:10ah
19:11do different things with the database
19:13that means the instances of the database
19:15so
19:16there are authorizations to read
19:18insert update and delete
19:20so read is where you can access the data
19:23but you cannot modify insert is when you
19:25can add new data but you
19:28do not with insert
19:30writes authorization you cannot
19:32update an existing data you can only
19:34insert data you can have
19:36update rights ah where we can change
19:38make modifications but you may not be
19:40you are not allowed to delete data and
19:43you can there could be a delete right
19:45where ah it allows you to delete data
19:48and mind you these authorizations are
19:50um
19:51not
19:53these are all independent authorization
19:55so you may have i mean certain
19:58authorizations may need certain other
20:00authorizations
20:01to be present for example if you are
20:03updating naturally you will need to read
20:06but
20:07it is these are all independent
20:09authorizations and you may have one or
20:12more of them to be able to do the
20:13appropriate actions
20:16similar set of
20:18another set of authorizations will exist
20:20if you want to for those who want to
20:22modify the database schema naturally ah
20:25this
20:25is primarily for the
20:28applications
20:30and
20:31application
20:32programmers and this primarily would be
20:36for the analysts
20:37that you can
20:39ah
20:40index the different table you can
20:43ah
20:43do
20:44you can have authorization for resources
20:47which mean you can create new relations
20:48create new schemas you can alter schemas
20:51you can drop schemas and so on so these
20:53are the different kinds of
20:54authorizations that are possible
20:57so let us see how it works the
20:59authorization is specified in terms of a
21:02statement called grant
21:04so you grant an authorization
21:07to a privilege list
21:10and on certain relation
21:15to a
21:16group of users so grant
21:20what you are what kind of authorization
21:22you are granting that is the previous
21:24list
21:25on what relation on view you are
21:27granting that
21:28is a on condition
21:30and to whom are you granting those
21:34so
21:40user list could be a specific user id or
21:43you could say public which in this case
21:45everybody will have that or this could
21:47be a role which will see what what a
21:49role is
21:51granting a privilege on a view does not
21:53imply granting any privileges on the
21:55underlying relation please mind this
21:59this one
22:00because
22:01you have seen that a view can be formed
22:03from multiple different relations so if
22:06somebody has been granted a
22:10a particular privilege say read
22:12privilege on a view
22:15then it does not mean that the
22:17corresponding underlying relation say
22:20you have been granted a
22:21read
22:22privilege on faculty relation that we
22:24faculty view that we did that does not
22:27mean that the user will automatically
22:29get a
22:31read privilege on the underlying
22:34instructure relation so that has to be
22:38kept in mind
22:40the granter of the privilege must
22:41already hold the privilege that is you
22:43cannot grant naturally grant will be
22:45done also by somebody in
22:47some of the users it may be
22:49so that user who is granting must also
22:53have the
22:54privilege same privilege on the specific
22:56item so you cannot grant privilege on
22:58some thing
23:00ah some relation or view on which you
23:03yourself do not have that or it has to
23:05be the database administrator who
23:07naturally has privilege for everything
23:11ah the privileges on sql there is a
23:14select privilege which is uh so you know
23:17this is a privilege list select
23:19privilege which basically means read
23:21access so it is saying select privilege
23:23as read access on instructor and these
23:26are the different users so this is how
23:28typically the
23:32you can have insert to ability to insert
23:35tuples
23:36the upload privilege this should be easy
23:38to understand now delete privilege so ah
23:41only thing is read is ah called select
23:44here so these are the different
23:45privileges that you have in sql and you
23:48have one all encompassing privilege
23:51which is called all privileges so there
23:53is a short form of allowing all these
23:55allowable privileges
23:58certainly if you can grant an
23:59authorization there needs to be a
24:02reverse process that is if you want to
24:04withdraw authorization
24:06of certain privileges
24:09on certain items for certain users so
24:12that is known as a revoked statement so
24:14you can revoke it the structure looks
24:17exactly similar to the grant so you
24:19revoke a privilege list select insert
24:22this kind of on certain relation and
24:24view from a set of users so you can say
24:28that revoke select on branch
24:30from this so once this is done then e1
24:34u2 and u3 will not be able to read the
24:36branch relation of the branch view
24:40the privileged list may be all to revoke
24:42all privileges so instead of
24:46revoking select insert separately you
24:48can just say all and revoke all of that
24:51the list of
24:52revokie can include public which means
24:56all users lose that privilege
24:59and
25:00or
25:01those who are granted if the same
25:03privilege is granted twice that is
25:05possible that
25:06a user gets a privilege granted to him
25:09or her
25:10by two different
25:13granting authorities
25:15then the if once is one of them is
25:19revoked the other will still continue to
25:21remain so
25:22every privilege that is granted
25:25needs to be explicitly revoked that's a
25:27basic meaning
25:29so all privileges that depend on the
25:31privilege being revoked are also revoked
25:33so some privilege which is dependent on
25:36some other privilege if
25:38ah if you if you revoke the update um
25:42privilege then the select privilege will
25:44remain
25:45but if you revoke the select privilege
25:48then if you also had the update
25:49privilege that will certainly get
25:51revoked because if you cannot
25:54read then naturally you cannot change
25:59the ah sql also allows you to create
26:02certain roles roles are kind of like
26:05virtual users so
26:06ah we all say that we all play certain
26:09roles so i have an entity as an
26:12individual say
26:13ah i may be a user called ppd
26:17but i have a role as an instructor
26:20i have a role
26:21as a
26:23say the head of the department i have a
26:25role
26:26as a chairman of a committee and so on
26:29so often times it becomes easier to
26:34grant
26:36privileges to different roles you do not
26:39really care
26:41immediately about
26:43who that individual could be who that
26:45particular user could be
26:47who
26:48has that
26:49privilege whoever
26:51plays that role gets that privilege
26:53whoever becomes the director of iit
26:55kharagpur has the privilege to
26:59appoint faculty members its of that kind
27:02it does not specifically so role is of
27:04that kind of a concept so you create
27:06role here we are saying that the role is
27:08ah
27:10a role instructor is created
27:12and then you are saying that you grant
27:14instructor to omit which says that
27:18amit now plays the role of instructors
27:20so any privilege that the instructor
27:23role has amit will enjoy that
27:27so let us see more of this
27:29the privileges can be grant to roles
27:32so earlier we said it could be public it
27:34could be users
27:35but now we are saying that it could be
27:37two roles so here this role was created
27:41and the privilege is being granted to
27:43that and since amit plays that role it
27:45will mean that um with this grant select
27:48on takes to instructor amit will
27:50actually get a privilege of select
27:54on text relation
27:56that is the kind of
27:58derived structure that roles give you
28:00roles can be granted to users as well as
28:02to other roles so roles are becoming
28:04like virtual users
28:06so you can
28:08create a role teaching assistant
28:10and
28:11grant
28:12teaching assistant to instructor which
28:14means
28:15that
28:16if you do that you are granting this so
28:19it means that any privilege the teaching
28:22assistant will have the instructor will
28:25get those privileges
28:26because you have
28:28made
28:29an instructor
28:31to also play the role teaching assistant
28:33mind new instructor itself is a virtual
28:35entity so if amit is an instructor
28:38by this
28:40then amit plays this role and this role
28:43plays teaching assistant role and this
28:45teaching assistant role has certain
28:46privileges so naturally
28:48through this chain process omit will get
28:51those privileges
28:53so in a instructor inherits all
28:55privileges of teaching assistant
29:00so this is what exactly what i was
29:01talking of we can have a chain of roles
29:04create a role dean new one we have
29:06created then grant instructed to dean
29:09grant dean to satoshi so which means
29:12that
29:13once you grant dean to instructor so
29:16anybody who plays the dean's role ah
29:19will get all privileges of instructor
29:22here you are saying that shatoshi is
29:25going to play the
29:27grand dean role so the satoshi in
29:31terms of chaining gets all the
29:33privileges that instructor has
29:40so once this has been done then you can
29:43have authorization on views as well so
29:46you have created a view here
29:48so this is a this is a view created the
29:52geo instructor the geology instructor
29:55and on that view particularly you have
29:57given
30:01the privilege to the jio staff
30:04so
30:05a geo staff member would be able to
30:08access this view and
30:12if
30:13this query is fired by a jio staff
30:17member
30:19which i am assuming is a role then this
30:23view will get executed and the results
30:26of all instructors in the department
30:28geology will be obtained
30:30but
30:31what if
30:34the jio staff does not have permission
30:36on instructor does not matter that is
30:39the beauty of the whole thing
30:41the geo staff may not have permission to
30:43do select on instructor
30:45but the jio staff has
30:48permission to
30:49select on jio instructor so
30:51the jio staff will be able to execute
30:55this view but the jio staff will not be
30:58able to do a select on
31:00from the instructor database instructor
31:03table
31:12there are several
31:13other authorization features the
31:15references privilege to create
31:18foreign key so we talked about the basic
31:21read write data manipulation privileges
31:24ah but there could be other privileges
31:26like whether you can create a foreign
31:29key
31:29whether you can transfer ah of privilege
31:33so
31:34whether you can
31:36give one privilege to the to another so
31:39they can cascade where they can restrict
31:41and so on
31:42so transfer of privileges is also
31:45privileged i mean actually you have to
31:47think of this is an authorization so
31:49actually what
31:50we can authorize is also a privilege
31:52that needs to be authorized
31:54so these are the derived privileges that
31:57are required
31:58so in summary ah we have
32:02learnt about
32:03sql expressions to deal with the
32:06integrity constraints
32:08we are familiarized with more data types
32:11ah particularly
32:13ah user defined types and domains
32:16creation of index
32:19and we have discussed about the
32:20authorization in sql each one of them
32:22particularly authorization has lot more
32:25details but
32:27at this intermediate level we just
32:28wanted to get a basic idea about
32:31authorization to be able to deal with
32:33that
32:34so with this we close our discussion on
32:37the intermediate level sql features
32:40in the next module
32:43that we start next week we will talk
32:45about some of the advanced sql features

About this transcript

This page contains the full transcript of Intermediate SQL/2 by Data Base Management System - IITKGP, generated from the public captions YouTube serves with the video. The transcript has 4,612 words across 850 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.