![ENTITY &ATTRIBUTESComposite vs SimpleSingle vs MultivaluedStored vs DerivedComplex AttributesAttributes that can be represented by grouping composite attributesbetween (), separating the components with commas, and by displaying themultivalued attributes between [] are known as complex attributes.{Address_phone({Phone(Area_code,Phone_number)},Address(Street_address{Number,Street, flat_number),City,State,Zip))}www.edureka.co](/image.pl?url=https%3a%2f%2fimage.slidesharecdn.com%2fpostgresqltutorialforbeginners-191024104631%2f75%2fPostgreSQL-Tutorial-For-Beginners-Edureka-22-2048.jpg&f=jpg&w=240)
Uploaded byEdureka!
2,079 views
PostgreSQL Tutorial For Beginners | Edureka
The document discusses various types of Database Management Systems (DBMS), particularly focusing on SQL and PostgreSQL, an advanced open-source relational database. It explains SQL commands, attributes, normalization forms, and different facets of database constraints and joins. Key topics include transaction control, data integrity, types of attributes, and the organization of database tables to minimize redundancy.
Recommended
![Coded Agents – with UiPath SDK + LangGraph [Virtual Hands-on Workshop]](/image.pl?url=https%3a%2f%2fcdn.slidesharecdn.com%2fss_thumbnails%2fcodedagentsdeck-251215155422-5497c599-thumbnail.jpg%3fwidth%3d640%26amp%3bheight%3d640%26amp%3bfit%3dbounds&f=jpg&w=240)
PostgreSQL Tutorial For Beginners | Edureka
- 2.TYPES OF DBMSWHATIS SQL?WHAT IS POSTGRESQL?FEATURES OF POSTGRESQLSQL COMMAND CATEGORIESINSTALL POSTGRESQLwww.edureka.co
- 3.
- 4.
- 5.
- 6.A standardizedprogramming languagewhichis used formanaging relationaldatabases.With SQL, you canmodify databases, add,update or delete rowsof data, retrievesubsets of informationfrom a database and,any moreRelational databaseslike MySQL Databases,oracle, Ms SQL Server,Sybase etc use SQL.Queries and other SQLoperations are writtenas statements.Example: select, insert,add, update, delete,create, alter, truncate.www.edureka.co
- 7.
- 8.PostgreSQLis an open-sourceobject relational databasesystem with a 30+ years of active development in the industry.World’s Most Advanced Open Source Relational Databasewww.edureka.co
- 9.
- 10.Data Types DataIntegrity PerformanceReliability Security Extensibilitywww.edureka.co
- 11.
- 12.
- 13..Transaction ControlLanguage(TCL)COMMIT, ROLLBACK,SAVEPOINTDataControlLanguage(DCL)GRANT, INVOKEData ManipulationLanguage(DML)SELECT, INSERT,UPDATE, DELETECREATE, DROP, ALTERData Definition Language(DDL)Includes the commands whichmainly deal with thetransaction of database.Includes commands whichmainly deals with the rights,permissions and other controlsof the database system.The SQL commands that dealswith the manipulation of datapresent in database.Consists of the commands thatcan be used to define thedatabase schema.www.edureka.co
- 14.
- 15.
- 16.
- 17.
- 18.Composite vs SimpleSinglevs MultivaluedStored vs DerivedComplex AttributesENTITY &ATTRIBUTESwww.edureka.co
- 19.ENTITY &ATTRIBUTESComposite vsSimpleA composite attribute can be divided into smaller subparts. These subpartsrepresent individual basic attributes with their own meanings.Attributes which are not divisible are simply known as simple or atomicattributes.AddressStreet _address City State ZipNumber Street House_numberwww.edureka.co
- 20.ENTITY &ATTRIBUTESComposite vsSimpleSingle vs MultivaluedAttributes having single value for a particular entity are known as single-valued attributes.A multivalued attribute may have lower and upper bounds to constrain thenumber of values allowed for each individual entity.www.edureka.co
- 21.ENTITY &ATTRIBUTESComposite vsSimpleSingle vs MultivaluedStored vs DerivedAttributes which are derived from the real entities are known as derivedattributes.The stored attribute are such attributes which are already stored in thedatabase and from which the value of another attribute is derived iscalled stored attribute.Age Birth_datewww.edureka.co
- 22.ENTITY &ATTRIBUTESComposite vsSimpleSingle vs MultivaluedStored vs DerivedComplex AttributesAttributes that can be represented by grouping composite attributesbetween (), separating the components with commas, and by displaying themultivalued attributes between [] are known as complex attributes.{Address_phone({Phone(Area_code,Phone_number)},Address(Street_address{Number,Street, flat_number),City,State,Zip))}www.edureka.co
- 23.
- 24.
- 25.
- 26.NOT NULL UNIQUECHECK DEFAULT INDEXwww.edureka.coEnsures that a NULLvalue cannot bestored in a columnThis constraintmakes sure that allthe values in acolumn aredifferentThis constraintensures that all thevalues in a columnsatisfy a specificcondition.This constraintconsists of a set ofdefault values for acolumn when novalue is specified.This constraint isused to create andretrieve data fromthe database veryquickly.www.edureka.coCONSTRAINTS IN DATABASE
- 27.
- 28.A technique thatorganizes tables in such a way that redundancy and dependency of data is reduced.BCNF3 NF2 NF1 NFwww.edureka.co
- 29.Salutation Full NameAddress Movies Rented CategoryMs. Alice Johnson 1st Street House No 3 Mission Impossible:Fallout, Clash of TitansAction, ActionMr. David Allen 3rd Street 45 Interstellar, Edge OfTomorrowSci-Fi, Sci-FiMr. David Allen 7th Avenue Mission Impossible:FalloutActionwww.edureka.co
- 30.NORMALIZATIONFirst Normal Form2NF3 NFBCNFEach table cell should have a single value. So, basically all the records must be unique.Salutation Full Name Address Movies RentedMs. Alice Johnson1st Street HouseNo 3MissionImpossible: FalloutMs. Alice Johnson1st Street HouseNo 3Clash of TitansMr. David Allen 3rd Street 45 InterstellarMr. David Allen 3rd Street 45 Edge Of TomorrowMr. David Allen 7th AvenueMissionImpossible: Falloutwww.edureka.co
- 31.NORMALIZATION3 NFBCNFDatabase shouldbe 1NF and should also have single column primary key.1 NFSecond Normal FormSalutation Full Name Address Movies_RentedID Salutation Full Name Address1 Ms AliceJohnson1st StreetHouse No32 Mr David Allen 3rd Street453 Mr David Allen 7th AvenueID Movies_Rented1 Mission Impossible:Fallout1 Clash of Titans2 Interstellar2 Edge Of Tomorrow3 Mission Impossible:FalloutTable 1Table 2www.edureka.co
- 32.NORMALIZATIONBCNFThe database shouldbe in 2NF and must not have any transitive functional dependencies.1 NF2 NFThird Normal FormID Full Name Address Salutation_ID ID Movies_RentedSalutation_ID Salutation1 Mr.2 Ms.3 Mrs.4 Dr.Table 1 Table 2Table 3www.edureka.co
- 33.NORMALIZATION1 NF2 NF3NFBoyce-Codd NormalFormIf your database is in 3rd Normal Form, there would be some scenarios where anomalieswould be present, if you have more than candidate key. Then BCNF comes into role, whereyou divide your tables further so that there would be only one candidate key present.www.edureka.co
- 34.
- 35.
- 36.ARITHMETICOPERATORS+, -, *,/, %BITWISEOPERATORS&, |, ^COMPARISONOPERATORS==, <, >, <=, >= <>COMPOUNDOPERATORS+=, *=, -=, /=,%=,&=, ^-=, |*=www.edureka.co
- 37.
- 38.Nested queries areSQL queries which have an outer query and inner subquery. The subquery is a query nested within anotherquery such as SELECT, INSERT, UPDATE or DELETE.SELECT lastname, firstnameFROM employeesWHERE addressCode IN (SELECT addressCodeFROM officeWHERE country = “INDIA”)OUTER QUERY SUBQUERY OR INNER QUERYwww.edureka.co
- 39.
- 40.UNIONCombines rows fromboththe queriesINTERSECTKeeps only those rows whichare common in both thequeries.MINUSKeeps rows from the left querywhich are not included in theright queryLEFT QUERY RIGHT QUERY FINAL RESULTwww.edureka.co
- 41.Copyright © 2017,edureka and/or its affiliates. All rights reserved.www.edureka.co
- 42.
- 43.
- 44.
- 45.
- 46.JOINS in SQLare commands which are used to combine rows from two or more tables, based on a related columnbetween those tables. They are predominantly used when a user is trying to extract data from tables which haveone-to-many or many-to-many relationships between them.What are Joins?www.edureka.co
- 47.TYPES OFJOINSINNER JOINFULLJOINLEFT JOINRIGHT JOINwww.edureka.co
- 48.
- 49.A view inSQL is a single table, which is derived from other tablesQueryROWSVIEWSCOLUMNSwww.edureka.co
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.