IntroductiontoDatabases

DanielaPuiuApplicationsSpecialistCenterfortheStudyofBiologicalComplexity,VCUdpuiu@804-827-0952qq群發(fā)GeneralConcepts DatabasedefinitionOrganizedcollectionoflogicallyrelateddataDataKnownfactsTypes:text,graphics,images,sound,videosDatabasemanagementsystem(DBMS)SoftwarepackagefordefiningandmanagingadatabaseDatabaseExamples ClassrosterHospitalpatientsLiterature(publishedarticlesinacertainfield)GenomicinformationProteinstructureTaxonomySinglenucleotidepolymorphismExample:MicrobialDatabase Organism:NameAccessionnumberGenomesizeGC%ReleasedateGenomecenterSequenceGene(proteincodingregions):NameAccessionnumberOrganismLocationonthechromosome(start,end)StrandSizeProductSequenceDataabouttheproteincodingregionsinthemicrobialgenomessequencedsofar.DatabaseModelsFlatfiles ‘60Hierarchical ‘60Network ‘70Relational ‘80Objectoriented ‘90Objectrelational ‘90Webenabled ‘90DatabaseTypes(cont.)TypeTypicalnumberofusersTypicalarchitectureTypicalsizePersonal1Desktop/Laptop/PDAMBWorkgroup5-25Client/server:2tierMB-GBDepartment25-100Client/server:3tierGBEnterprise>100Client/server:distributedGB-TBInternet>1000Websever&applicationservers MB-GBFlatFilesCharacteristics:DataisstoredasrecordsinregularfilesRecordsusuallyhaveasimplestructureandfixednumberoffieldsForfastaccessmaysupportindexingoffieldsintherecordsNomechanismsforrelatingdatabetweenfilesOneneedsspecialprogramsinordertoaccessandmanipulatethedataFlatFilesExampleMicrobialdatabase:Genbankformat:EscherichiacoliK12StreptococcuspneumoniaeR6…Fastaformat:multiplefilesEscherichiacoliK12:genome,genes,genepositionsStreptococcuspneumoniaeR6:genome,genes,genepositions…Datamanipulation:Sequenceextraction,searchIndexingFormatconversion…RelationalDatabaseCharacteristics:Dataisorganizedintotables:rows&columnsEachrowrepresentsaninstanceofanentityEachcolumnrepresentsanattributeofanentityMetadatadescribeseachtablecolumnRelationshipsbetweenentitiesarerepresentedbyvaluesstoredinthecolumnsofthecorrespondingtables(keys)AccessiblethroughStandardQueryLanguage(SQL)Enterprisedatamodel GraphicalrepresentationofthehighlevelentitiesExample:MicrobialdatabaseeachorganismhasmultiplecorrespondinggenesOne:ManyrelationOrganismGene1mMetadataDatathatdescribesthepropertiesorcharacteristicsofotherdataDoesnotincludesampledataAllowsdatabasedesignersanduserstounderstandthemeaningofthedataMetadata&DataTableNameTypeMaxLengthDescriptionNameAlphanumeric100OrganismnameSizeInteger10Genomelength(bases)GcFloat5PercentGCAccessionAlphanumeric10AccessionnumberReleaseDate8ReleasedateCenterAlphanumeric100GenomecenternameSequenceAlphanumericVariableSequenceOrganismNameSizeGcAccessionReleaseCenterSequenceEscherichiacoliK124,640,00050NC_00091309/05/1997Univ.WisconsinAGCTTTTCATT…StreptococcuspneumoniaeR62,040,00040NC_00309809/07/2001EliLillyandCompanyTTGAAAGAAAA……Metadata&DataTable(cont.)NameTypeMaxLengthDescriptionNameAlphanumeric100GenenameAccessionAlphanumeric10GeneaccessionnumberOAccesionAlphanumeric10OrganismaccessionnumberStartInteger10GenestartEndInteger10GeneendStrandCharacter1GenestrandProductAlphanumeric1000GeneannotationSequenceAlphanumericVariableGenesequenceGeneNameAccessionOAccessionStartEndStrandProductSequencethrL16127995NC_000913190255+theoperonleaderpeptideMKRI…thrA16127996NC_0009133372799+homoserinedehydrogenaseIMRVL…transposase_A15902058NC_0030982020720554+transposaseMWYN…Relationships UsedtoconnecttablesField(s)thathavethesamevalueintherelatedtablesOrganism.Accession=Gene.OAccessionOrganism.AccessionUniquePrimarykeyGene.OAccessionNotuniqueSecondarykeySQLANSI(AmericanNationalStandardsInstitute)standardcomputerlanguageforaccessingandmanipulatingdatabasesystems.SQLstatementsareusedtoretrieveandupdatedatainadatabase.Includes:DataManipulationLanguage(DML)DataDefinitionLanguage(DDL)DataManipulationLanguageSyntaxforexecutingqueries,updating,inserting,anddeletingrecords.SELECT-extractsdatafromoneormoretableINSERTINTO-insertsnewdataintoatableUPDATE-updatesdatainatableDELETEFROM-deletesdatafromatableDMLExampleSelectallEscherichiacoliK12geneswhichareinthe1MB-2MBregionofthechromosome: SELECT* FROMOrganism,Gene WHERE Organism.Name=“EscherichiacoliK12”AND Organism.Accession=Gene.OAccessionAND Gene.Start>=1,000,000AND Gene.End<=2,000,000DMLExample(cont.)INSERTINTOGene(Name,Accession,OAccession,Start,End,Strand,Sequence)VALUES(“thrL”,16127995,”NC_000913”,190,255,’+’,”throperonleaderpeptide”,“MKRI…”)UPDATEGeneSETStart=160WHEREAccession=”NC_000913”DELETEFROMGeneWHEREAccession=”NC_000913”DataDefinitionLanguageSyntaxforcreating,editing,deleting:DatabasesTablesViewsIndexesConstraintsUsersPrivilegesDDLExamplesCREATEDATABASEMicrobial;CREATETABLEOrganism( Namevarchar(100) Sizeint(10) Gc decimal(5) Accessionvarchar(10) Releasedate(8) Centervarchar(100));ALTERTABLEOrganismADDSequencevarchar;DROPTABLEOrganism;DBMSSoftwarepackagefordefiningandmanagingadatabase.Examples:Proprietary:MSAccess,MSSQLServer,DB2,Oracle,SybaseOpensource:MySql,PostgreSQLDBMSAdvantages Program-dataindependenceMinimaldataredundancyImproveddataconsistency&quality